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 oneon oneoff 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 oneon oneoff >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 oneon oneoff > >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. Rightclick 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 oneon oneoff >> >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. > > Rightclick 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 oneon oneoff > >> >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? 