Formatting rows

[noblight]

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.

 

[Gord Dibben]

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

 

[noblight]

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]

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 Insert>Module. 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 Tool>Macro>Macros.

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


Gord

 

[noblight]

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]

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 F2>Enter to re-color.


Gord

 

[noblight]

I followed the same sequence you laid out earlier, but when I added the new
module and went to "Macros" to run it, no macro appears in the list.

Also I didn't mention it last time, but thought it might be
significant...when I tried to save the module, I got an Excel dialog box that
told me "The following features cannot be saved in macro-free workbooks: VB
Project. To save a file with these features, Click No, and then choose a
macro-enabled file type in the File Type list. To continue saving as a
macro-free workbook, click Yes."

I just clicked Yes when I used the first code you sent and it worked fine so
I ignored the macro-free bit. This time I tried both macro-free AND .xlsm,
but in neither case did the macro show up so I could run it.

I do appreciate your time.

Thanks.

 

[noblight]

Ooops, I see how it works now--no need to "Run" the macro. Just like I wanted
it.

Thanks very much!

 

[noblight]

If you can tolerate one more question...!

I decided to move the data that this sorted on to Column B from A. I
tinkered around for a while trying to change this and that in the code, but
can't figure out where you told it to look in Column A for any changes.

Thanks.

 

[Gord Dibben]

Apologies for not giving new instructions with new code.

The latest code I posted is worksheet event code and does not go into a
General module as the first macro did.

Copy the code again.

Right-click on the sheet tab and "View Code"

Paste the code into that sheet module.

The code will run automatically when you enter something in column A

Remove the other general module.

Save workbook as *.xlsm


Gord

 

[Gord Dibben]

Private Sub Worksheet_Change(ByVal Target As Range)
'color rows with change in data in column B
'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, 2), _
Cells(Rows.Count, 2).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


Gord