alternating cell colour

[mohd21uk via OfficeKB.com]

I have a spreadsheet where column A has a set of different values. I would
like to alternate the cell colour for changing values.
For e.g

210 - Cell Colour Green
210 - Cell Colour Green
210 - Cell Colour Green
215 - Cell Colour Blue
215 - Cell Colour Blue
220 - Cell Colour Green
221 - Cell Colour Blue
e.t.c

I would be grateful if you could provide me a macro that would do just this.

 

[Bob Phillips]

Sub Test()
Dim iLastRow As Long
Dim i As Long
Dim ci As Long

ci = 10
iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
Range("A1").Interior.ColorIndex = ci
For i = 2 To iLastRow
If Cells(i, "A").Value <> Cells(i - 1, "A").Value Then
ci = IIf(ci = 10, 5, 10)
End If
Cells(i, "A").Interior.ColorIndex = ci
Next i

End Sub

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

 

[Jim Cone]

The free Excel add-in 'Shade Data Rows' could do what you want.
Download from... http://www.realezsites.com/bus/primitivesoftware
--
Jim Cone
San Francisco, USA


I have a spreadsheet where column A has a set of different values. I would
like to alternate the cell colour for changing values.
For e.g
210 - Cell Colour Green
210 - Cell Colour Green
210 - Cell Colour Green
215 - Cell Colour Blue
215 - Cell Colour Blue
220 - Cell Colour Green
221 - Cell Colour Blue
e.t.c
I would be grateful if you could provide me a macro that would do just this.