# EXCEL: How to conditional format a thick bottom border to an entire row based on one column value? [duplicate]

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!

## migrated from stackoverflow.comDec 27 '12 at 6:06

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

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

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

``````=\$A1<>\$A2
``````

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