Conditional fomatting formula to match positive and negative amounts in a single column.

The following code will, I believe, do what you need:

Sub OffsetFound()

    Dim I As Long

    Dim last As Long

    Dim cell As Range

    last = Selection.Row + Selection.Count - 1

    For Each cell In Selection

        With cell

            If .Font.Strikethrough = False Then

            I = 1

                Do

                    If .Offset(I, 0) = -cell And .Offset(I, 0).Font.Strikethrough = False Then

                        .Font.Strikethrough = True

                        .Offset(I, 0).Font.Strikethrough = True

                        Exit Do

                    End If

                    I = I + 1

                Loop Until .Offset(I, 0).Row > last

            End If

        End With

    Next cell

End Sub

If this answer solves your problem, please check, Mark as Answered. If this answer helps, please click the Vote as Helpful button. Cheers Shane Devenshire

Unless I am implementing Teethless mama's conditional formatting formula incorrectly, it (and all the posted variations derived from it) appears to only "strike through" the first of the positive/negative paired values... the solution by Mike H does the "strike through" to both the positive and negative values.

I have no problem to get the formatting as expected, i.e. striking out positive and negative values "pairwise" leaving any unmatched unformatted.

Here is an example of data and formatting (red background used instead of strikethrough for readability reasons) using the formatting formula:

=SMALL(IF($A$2:$A$21=-A2,1),COUNTIF($A$2:A2,A2))

Regards / Lars-Åke

The following code will, I believe, do what you need:

Sub OffsetFound()

    Dim I As Long

    Dim last As Long

    Dim cell As Range

    last = Selection.Row + Selection.Count - 1

    For Each cell In Selection

        With cell

            If .Font.Strikethrough = False Then

            I = 1

                Do

                    If .Offset(I, 0) = -cell And .Offset(I, 0).Font.Strikethrough = False Then

                        .Font.Strikethrough = True

                        .Offset(I, 0).Font.Strikethrough = True

                        Exit Do

                    End If

                    I = I + 1

                Loop Until .Offset(I, 0).Row > last

            End If

        End With

    Next cell

End Sub

 

---

If this answer solves your problem, please check, Mark as Answered. If this answer helps, please click the Vote as Helpfulbutton. Cheers Shane Devenshire

I suggest that you put in a clearing of strike throughs at the beginning if you want to run the macro again after changing some data in the selected range.

For each cell in Selection

   cell.Font.Strikethrough = False

Next cell

Regards / Lars-Åke

Rob,

Select the range and apply this conditional format using font strikethrough and I think this does what you want in a single column

=COUNTIF($A$1:$A$11,A1)+COUNTIF($A$1:$A$11,-1*A1)=2

If this post answers your question, please mark it as the Answer.

Mike H