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

Anonymous

Hello,

I have a single column of both positive and negative amounts. I am looking for a formula to insert in the conditional formatting option that will show a "strikethrough" effect for those that match. I have inserted another column and added the absolute function to those amounts, sorted them, and used the "countif" formula, strikethrough effect in the conditional formatting to accomplish this. However, I was wondering if I could accomplish this without adding this second column.

Thanks for looking at my request.

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

Anonymous

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

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

0 comments

Answer accepted by question author

Anonymous

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

0 comments

Answer accepted by question author

Anonymous

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

0 comments

Answer accepted by question author

Anonymous

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

0 comments

49 additional answers

Anonymous

2017-01-02T22:58:03+00:00

I've modified my code to make it automatic and correct a omisson:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim I As Long

    Dim last As Long

    Dim cell As Range

    Dim isect As Range

    Dim CreditDebit As Range

    Set CreditDebit = Range("A1:A100")

    Set isect = Application.Intersect(Target, CreditDebit)

    If Not isect Is Nothing Then

        CreditDebit.Font.Strikethrough = False

        last = CreditDebit.Row + CreditDebit.Count - 1

        For Each cell In CreditDebit

            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 If

End Sub

You should change the line Set CreditDebit = Range("A1:A100") to match the range where your data is.

To use this code press Alt+F11, in the VBA project window at the top left locate your file and then the sheet you where you debits and credit are. Double-click that sheet to expose the coresponding code sheet on the right. Copy and past the above code into that sheet.

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

when i run the code or write down any numbers to run the code i saw that in VBA highlighted the line below and pop up msg (run time error 13 type mismatched) i already uses office 13

If .Offset(I, 0) = -cell And .Offset(I, 0).Font.Strikethrough = False Then
Was this answer helpful?

0 comments No comments