Format row based on value

[Martin]

Hello,

I have this code that look at the values in column 2 for each row specified
in the code (2 to 9216). I want it to colour the row everytime the value
changes. This does this perfectly except it doesnt colour the row then the
value first changes. So for example if row 2 to 6 are the same and row 7 to
10 are the same, rows 8 to 10 are formatted.

Can anyone help with this?

Dim lRow As Long
Dim lCol As Long

For lRow = 2 To 9216

If Cells(lRow, 2).Value = Cells(lRow - 1, 2) Then
If IsEmpty(Cells(lRow, 256)) Then
lCol = Cells(lRow, 256).End(xlToLeft).Column
Else
lCol = 256
End If
Range(Cells(lRow, 1), Cells(lRow, lCol)).Interior.ColorIndex = 36
End If
Next

This is a repost from earlier today, I hope I have worded it better!

Martin

 

[Nigel]

A quick fix - change the first If statement as follows.....

If Cells(lRow, 2).Value = Cells(lRow - 1, 2) Or lRow = 2 Then

 

[Martin]

Hi,

Thanks, I see what you mean however the formatting needs to take place over
10,000 lines and row 2 will have no relevance to say row 1,500.

What I need is to say by code for example is if current IRow, column 2 is
different to the row above, column 2 then do the formatting

 

[Nigel]

You need to explain what you are trying to achieve? As I see it coloring
the first row of each group will color all rows! Or do you have blank rows
you do not wish to color?



--

Regards,
Nigel
(e-mail address removed)

 

[Martin]

Hi,

Here is a sample of the data I have:

Column Heading
Area 1
Area 1
Area 1
Area 2
Area 2
Area 2
Area 2
Area 3
Area 3
Area 3
Area 3

I want to leave the rows with the value of "Area 1" as normal. If the row
then changes to another name, in this example "Area 2" then format the rows
yellow. If the value changes again, in this example "Area 3" then leave the
rows as normal and so on and so on.

The code I posted below does this almost perfectly but does not colour the
first row when the change takes place i.e. the first row with "Area 2".