1

Possible Duplicate:
Excel conditional formatting to highlight entire row if one date is not equal to another date in that row

I have the following rows...

1   AA     12  13   4   7
1   A      12  13   5   2
1   A      12  14   5   2
1   AAA    1   15   3   1
2   AAA    1   15   3   1
2   AA     3   44   5   19
2   A      R   4    33  87
3   AA     3   3R   55  77

And I'm wanting to learn how to do conditional formatting so that if the left most column changes in value I can add a thick bottom border to the entire row. So for instance it would appear like this...

1   AA     12  13   4   7
1   A      12  13   5   2
1   A      12  14   5   2
1   AAA    1   15   3   1
-------------------------
2   AAA    1   15   3   1
2   AA     3   44   5   19
2   A      R   4    33  87
-------------------------
3   AA     3   3R   55  77

and so on...

I was able to get it to work, but only for the first column and not format the entire row. Thanks!

marked as duplicate by Daniel Beck Dec 28 '12 at 13:08

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

migrated from stackoverflow.com Dec 27 '12 at 6:06

This question came from our site for professional and enthusiast programmers.

8

You need to anchor the column references in the formula for the conditional format.

Assuming that your data range begins in A1, you would set a conditional format on the range A1:F1 with a criterion formula $A1<>$A2 and your chosen format.

Then, you would copy the formatting of the row down the range (using Paste Special Format).

  • Thanks! For the answer still new to excel! The $'s were what I was missing. – fwaokda Dec 27 '12 at 14:16
  • This was a great, answer, but I forgot that a formula in excel always starts with = - so I eventually realised I could: select the whole block and add conditional formatting, I just needed to make sure the box contained: =$A1<>$A2 :) – Ian Grainger Jan 15 '16 at 12:43
2

Supposing your top-left cell is A1, select all the data starting at A1 and then use this formula:

=$A1<>$A2
0

Here is some VBA that will perform what you are looking for.

Sub LineSep()

'   Start Location 1 lower than first range with value
Range("A2").Select

Do
    If ActiveCell.Offset(-1, 0).Value = ActiveCell.Value Then
    ActiveCell.Offset(1, 0).Select
        Else:
        Range(Selection, Selection.End(xlToRight)).Select
        With Selection.Borders(xlEdgeTop)
            .LineStyle = xlContinuous
            .ColorIndex = 0
            .TintAndShade = 0
            .Weight = xlMedium
        End With
        ActiveCell.Offset(1, 0).Select
    End If

Loop Until ActiveCell.Value = ""

End Sub

Not the answer you're looking for? Browse other questions tagged or ask your own question.