Prev: How do I customize hotkeys for excel 2007?
Next: Using "sort" in auto filter not possible when protected?
From: Gord Dibben on 14 Jan 2010 12:30
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

On Thu, 14 Jan 2010 05:09:02 -0800, noblight
<noblight(a)discussions.microsoft.com> 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 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 15 Jan 2010 00:15
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.






"Gord Dibben" wrote:

> 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
>
> On Thu, 14 Jan 2010 05:09:02 -0800, noblight
> <noblight(a)discussions.microsoft.com> 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 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 15 Jan 2010 00:40
Ooops, I see how it works now--no need to "Run" the macro. Just like I wanted
it.

Thanks very much!




"Gord Dibben" wrote:

> 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
>
> On Thu, 14 Jan 2010 05:09:02 -0800, noblight
> <noblight(a)discussions.microsoft.com> 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 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 15 Jan 2010 02:28
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" wrote:

> 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
>
> On Thu, 14 Jan 2010 05:09:02 -0800, noblight
> <noblight(a)discussions.microsoft.com> 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 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: Gord Dibben on 15 Jan 2010 11:22
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

On Thu, 14 Jan 2010 21:15:01 -0800, noblight
<noblight(a)discussions.microsoft.com> wrote:

>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.
>
>
>
>
>
>
>"Gord Dibben" wrote:
>
>> 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
>>
>> On Thu, 14 Jan 2010 05:09:02 -0800, noblight
>> <noblight(a)discussions.microsoft.com> 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 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.
>> >> >>
>> >> >> .
>> >> >>
>> >>
>> >> .
>> >>
>>
>> .
>>

First  |  Prev  |  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?