Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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!

share|improve this question

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

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

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.

3 Answers 3

up vote 6 down vote accepted

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).

share|improve this answer
    
Thanks! For the answer still new to excel! The $'s were what I was missing. –  fwaokda Dec 27 '12 at 14:16

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

=$A1<>$A2
share|improve this answer

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
share|improve this answer

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