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

Anonymous
2010-07-24T15:40:02+00:00

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.

Microsoft 365 and Office | Excel | For home | Windows

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
2010-07-24T22:44:02+00:00

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

Was this answer helpful?

10+ people found this answer helpful.
0 comments
Answer accepted by question author
Anonymous
2010-07-25T16:00:26+00:00

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

Was this answer helpful?

0 comments
Answer accepted by question author
Anonymous
2010-07-24T23:00:24+00:00

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

Was this answer helpful?

0 comments
Answer accepted by question author
Anonymous
2010-07-24T21:16:04+00:00

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

Was this answer helpful?

0 comments

49 additional answers

Sort by: Oldest
Most helpful Newest Oldest
  1. Anonymous
    2010-07-25T11:50:40+00:00

    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.

    Rob, you have already got a macro for this, but I could not give up on the challenge to do this by a conditional formatting formula as you originally asked for.

    In case you or someone else need a solution for this where you can't allow macros and without having any helper columns , here is my suggestion.

    Assuming the table of amounts is in cells A2:A21 use the following formula for conditional formatting:

    (cells A1, A22, and A23 should not contain any numbers. Cells A22 and A23 should not be empty if you want to be able to handle amounts equal to 0)

    =IFERROR(MATCH(ROWS($1:2),B$1:B1,0),IFERROR(SMALL(IF(A3:A$23=-A2,ROW(A3:A$23)), SUM((B$1:B1>ROWS($1:2))*(A$1:A1=A2))+1),0))

    (See Note below if you don't have Excel 2007 or later)

    Select cells A2:A21 and then Home tab->Styles section->Conditional Formatting->New Rule...->Use a formula to determine which cells to format

    Enter the above formula in the text field Format values where this formula is true, Click on Format... and choose your format. Click OK until all dialogues are closed.

    If you want to "see" how it works, you can put the formula it in cell B2 and copy it down to cell B21. But there is no other need for this column soyou can remove it later .

    Note: the formula is an array formula so it has to be confirmed with CTRL+SHIFT+ENTER rather than just ENTER.

    The first match checks if the "current" row number is already put somewhere "above" in this column. In that case just put that row's number on the current row.

    Otherwise look for numbers on rows "below" the current row that are the negative of the amount in the current row. Skip matches on rows that are already "noted" for on rows "above" the current row as they are already "reserved". Note/reserve the first match that is not already "reserved" by noting the matching row's number on the current row. If no match can be found note 0 for this row indicating that there is no match for this entry.

    When this formula has been "executed" for the entire range it will contain a 0 on the rows that are not matched. And on the rows that are matched the row number of the respective matching row will be found. The conditional formatting will format everything that is not 0, i.e that is matched.

    Here is an example on how it can look like where I use red cell color rather than strikethrough as formatting:

    Hope this helps / Lars-Åke

    Note: If you don't have Excel 2007 or later you can't use IFERROR(), but then you can translate it is like this using IF() and ISERROR():

    IFERROR(stuff1, IFERROR(stuff2,0)) -> IF(ISERROR(stuff1),IF(ISERROR(stuff2),0,stuff2),stuff1)

    This formula becomes rather long:

    =IF(ISERROR(MATCH(ROWS($1:2),B$1:B1,0)),IF(ISERROR(SMALL(IF(A3:A$23=-A2,ROW(3:$23)), SUM((B$1:B1>ROWS($1:2))*(A$1:A1=A2))+1)),0,SMALL(IF(A3:A$23=-A2,ROW(3:$23)), SUM((B$1:B1>ROWS($1:2))*(A$1:A1=A2))+1)),MATCH(ROWS($1:2),B$1:B1,0))

    Was this answer helpful?

    0 comments