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.

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.

Thanks for your response. I'm afraid I don't know what to do with the code
you've written. Can you tell me where to put it? Thanks much.

"Gord Dibben" wrote:

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.

.

If you're not familiar with VBA and macros, see David McRitchie's site for
more on "getting started".

http://www.mvps.org/dmcritchie/excel/getstarted.htm

or Ron de De Bruin's site on where to store macros.

http://www.rondebruin.nl/code.htm

In the meantime..........

First...create a backup copy of your original workbook.

To create a General Module, hit ALT + F11 to open the Visual Basic Editor.

Hit CRTL + r to open Project Explorer.

Find your workbook/project and select it.

Right-click and InsertModule. Paste the code in there. Save the
workbook and hit ALT + Q to return to your workbook.

Run or edit the macro by going to ToolMacroMacros.

You can also assign this macro to a button or a shortcut key combo.


Gord


On Tue, 12 Jan 2010 05:43:01 -0800, noblight
wrote:

Thanks for your response. I'm afraid I don't know what to do with the code
you've written. Can you tell me where to put it? Thanks much.

"Gord Dibben" wrote:

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.

.

That works! Thanks a lot.

2 questions:

I tried changing the colors around and figured out some of the color codes
to use, but can't get the alternating sequence right. Rather than grey and
white, I decided to try to get two colors to alternate but the best I've been
able to do is get only one color to alternate with white. Every other attempt
yielded everything in just one color. Any suggestions?

Also, is there a way to make it so that I don't have to manually run the
macro every time I update the spreadsheet?

Thanks again.




"Gord Dibben" wrote:

If you're not familiar with VBA and macros, see David McRitchie's site for
more on "getting started".

http://www.mvps.org/dmcritchie/excel/getstarted.htm

or Ron de De Bruin's site on where to store macros.

http://www.rondebruin.nl/code.htm

In the meantime..........

First...create a backup copy of your original workbook.

To create a General Module, hit ALT + F11 to open the Visual Basic Editor.

Hit CRTL + r to open Project Explorer.

Find your workbook/project and select it.

Right-click and InsertModule. Paste the code in there. Save the
workbook and hit ALT + Q to return to your workbook.

Run or edit the macro by going to ToolMacroMacros.

You can also assign this macro to a button or a shortcut key combo.


Gord


On Tue, 12 Jan 2010 05:43:01 -0800, noblight
wrote:

Thanks for your response. I'm afraid I don't know what to do with the code
you've written. Can you tell me where to put it? Thanks much.

"Gord Dibben" wrote:

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.

.


.

Remove the previous macro from the module you placed it in.

Add this code to the Sheet Module

Private Sub Worksheet_Change(ByVal Target As Range)
'color rows with change in data in column A
'grey, yellow, grey, yellow
Dim rngName As Range
Dim colIdx As Integer
Dim i As Long
'Following assumes column header in row 1
On Error GoTo endit
Application.EnableEvents = False
Application.ScreenUpdating = False
Set rngName = Me.Range(Cells(1, 1), _
Cells(Rows.Count, 1).End(xlUp))
colIdx = 15 'Grey........edit to suit
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 = 6 'yellow.....edit to suit
Else
colIdx = 15
End If
End If
.Cells(i).EntireRow.Interior.ColorIndex = colIdx
Next i
End With
endit:
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub

Note: if you do a Sort you will get inconsistent coloring. After the Sort
simply select any cell in column and F2Enter to re-color.


Gord

On Thu, 14 Jan 2010 05:09:02 -0800, noblight
wrote:

That works! Thanks a lot.

2 questions:

I tried changing the colors around and figured out some of the color codes
to use, but can't get the alternating sequence right. Rather than grey and
white, I decided to try to get two colors to alternate but the best I've been
able to do is get only one color to alternate with white. Every other attempt
yielded everything in just one color. Any suggestions?

Also, is there a way to make it so that I don't have to manually run the
macro every time I update the spreadsheet?

Thanks again.




"Gord Dibben" wrote:

If you're not familiar with VBA and macros, see David McRitchie's site for
more on "getting started".

http://www.mvps.org/dmcritchie/excel/getstarted.htm

or Ron de De Bruin's site on where to store macros.

http://www.rondebruin.nl/code.htm

In the meantime..........

First...create a backup copy of your original workbook.

To create a General Module, hit ALT + F11 to open the Visual Basic Editor.

Hit CRTL + r to open Project Explorer.

Find your workbook/project and select it.

Right-click and InsertModule. Paste the code in there. Save the
workbook and hit ALT + Q to return to your workbook.

Run or edit the macro by going to ToolMacroMacros.

You can also assign this macro to a button or a shortcut key combo.


Gord


On Tue, 12 Jan 2010 05:43:01 -0800, noblight
wrote:

Thanks for your response. I'm afraid I don't know what to do with the code
you've written. Can you tell me where to put it? Thanks much.

"Gord Dibben" wrote:

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.

.


.