Prev: How do I customize hotkeys for excel 2007?
Next: Using "sort" in auto filter not possible when protected?
From: noblight on 9 Jan 2010 17:55
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.
From: Gord Dibben on 9 Jan 2010 18:44
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
<noblight(a)discussions.microsoft.com> 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.

From: noblight on 12 Jan 2010 08:43
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
> <noblight(a)discussions.microsoft.com> 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.
>
> .
>
From: Gord Dibben on 12 Jan 2010 17:31
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


On Tue, 12 Jan 2010 05:43:01 -0800, noblight
<noblight(a)discussions.microsoft.com> 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
>> <noblight(a)discussions.microsoft.com> 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.
>>
>> .
>>

From: noblight on 14 Jan 2010 08:09
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 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
>
>
> On Tue, 12 Jan 2010 05:43:01 -0800, noblight
> <noblight(a)discussions.microsoft.com> 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
> >> <noblight(a)discussions.microsoft.com> 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.
> >>
> >> .
> >>
>
> .
>
 |  Next  |  Last
Pages: 1 2 3
Prev: How do I customize hotkeys for excel 2007?
Next: Using "sort" in auto filter not possible when protected?