Sub Alternate_Row_Color()
'color rows with change in data in column A
'grey, none, grey, none
Dim rngName As Range
Dim colIdx As Integer
Dim i As Long
'Following assumes column header in row 1
Set rngName = Sheets("Sheet1").Range(Cells(1, 1), _
Cells(Rows.Count, 1).End(xlUp))
colIdx = 15 'Grey
With rngName
.Cells(1, 1).EntireRow.Interior.ColorIndex = colIdx

'Starting at 2nd data row
For i = 2 To .Rows.Count
If .Cells(i) < .Cells(i - 1) Then
If colIdx = 15 Then
colIdx = xlColorIndexNone
Else
colIdx = 15
End If
End If
.Cells(i).EntireRow.Interior.ColorIndex = colIdx
Next i
End With

End Sub


Gord Dibben MS Excel MVP


On Sat, 9 Jan 2010 14:55:01 -0800, noblight
wrote:

I want white and shaded alternate rows, but not in the simple one-on one-off
pattern.

My spreadsheet is sorted by date in Column A. I may have 10 rows with a date
of 01/09/2010, then one row with 01/10/2010, then 20 rows with
01/11/2010...you get the idea.

I want all rows with a given date shaded, then all rows for the next date
(and there are gaps of more than one day between some dates) left white, then
all rows for the next date shaded, etc.

The purpose, of course, is to make it easy to distinguish all rows for a
given date at a glance. I have played with conditional formatting for hours
and can't get it right. Would appreciate any help.