Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formatting rows
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formatting rows
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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formatting rows
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. . |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formatting rows
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. . |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formatting rows
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. . . |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formatting rows
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. . . |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formatting rows
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 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. . . . |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formatting rows
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 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. . . . |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formatting rows
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 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. . . . |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formatting rows
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 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 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. . . . |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formatting rows
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 On Thu, 14 Jan 2010 23:28:01 -0800, noblight wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formatting Rows | New Users to Excel | |||
conditional formatting across rows | Excel Worksheet Functions | |||
Rows not formatting all the way down | Excel Worksheet Functions | |||
Conditional Formatting of Rows | Excel Discussion (Misc queries) | |||
Conditionally formatting rows | Excel Worksheet Functions |