Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Color rows by "group"
Hi All.....
I would like to alternate the row color from gray to none, for each group of rows according to the value in the cells in column G. That is, starting with row 11, I want no background color, then if G12=G11, likewise row 12 would have no color, but if G12<G11, then the background color of row 12 would switch to gray. Likewise progressing down column G, as the values change from the one above it, so does the row color, if the values are the same as the one above, the row color would stay the same as the one above. The entire column is sorted in order so there would be no like values showing up out of order. The length of column G will be unknown. Example row Col G Color 11 aa none 12 aa none 13 bb gray 14 cc none 15 cc none 16 cc none 17 dd gray 18 dd gray 19 ee none TIA Vaya con Dios, Chuck, CABGx3 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Color rows by "group"
Nigel, that is "'way beyond COOL", that is "OUTSTANDING TO THE MAX".
Thank you many, many times......... Vaya con Dios, Chuck, CABGx3 "Nigel" wrote: Try this.......I set it to process Sheet1, change as required. Sub Shader() Dim c As Range, xc As Integer xc = 15 With Sheets("Sheet1") For Each c In .Range("G11:G" & .Cells(.Rows.Count, "G").End(xlUp).Row) ' toggle fill If c.Value < c.Offset(-1, 0).Value Then If xc = 0 Then xc = 15 Else xc = 0 End If ' set fill With .Rows(c.Row).EntireRow.Interior .Pattern = xlSolid .ColorIndex = xc End With Next c End With End Sub -- Regards, Nigel "CLR" wrote in message ... Hi All..... I would like to alternate the row color from gray to none, for each group of rows according to the value in the cells in column G. That is, starting with row 11, I want no background color, then if G12=G11, likewise row 12 would have no color, but if G12<G11, then the background color of row 12 would switch to gray. Likewise progressing down column G, as the values change from the one above it, so does the row color, if the values are the same as the one above, the row color would stay the same as the one above. The entire column is sorted in order so there would be no like values showing up out of order. The length of column G will be unknown. Example row Col G Color 11 aa none 12 aa none 13 bb gray 14 cc none 15 cc none 16 cc none 17 dd gray 18 dd gray 19 ee none TIA Vaya con Dios, Chuck, CABGx3 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Color rows by "group"
Glad to be of help
-- Regards, Nigel "CLR" wrote in message ... Nigel, that is "'way beyond COOL", that is "OUTSTANDING TO THE MAX". Thank you many, many times......... Vaya con Dios, Chuck, CABGx3 "Nigel" wrote: Try this.......I set it to process Sheet1, change as required. Sub Shader() Dim c As Range, xc As Integer xc = 15 With Sheets("Sheet1") For Each c In .Range("G11:G" & .Cells(.Rows.Count, "G").End(xlUp).Row) ' toggle fill If c.Value < c.Offset(-1, 0).Value Then If xc = 0 Then xc = 15 Else xc = 0 End If ' set fill With .Rows(c.Row).EntireRow.Interior .Pattern = xlSolid .ColorIndex = xc End With Next c End With End Sub -- Regards, Nigel "CLR" wrote in message ... Hi All..... I would like to alternate the row color from gray to none, for each group of rows according to the value in the cells in column G. That is, starting with row 11, I want no background color, then if G12=G11, likewise row 12 would have no color, but if G12<G11, then the background color of row 12 would switch to gray. Likewise progressing down column G, as the values change from the one above it, so does the row color, if the values are the same as the one above, the row color would stay the same as the one above. The entire column is sorted in order so there would be no like values showing up out of order. The length of column G will be unknown. Example row Col G Color 11 aa none 12 aa none 13 bb gray 14 cc none 15 cc none 16 cc none 17 dd gray 18 dd gray 19 ee none TIA Vaya con Dios, Chuck, CABGx3 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Color rows by "group"
Now a follow on question, if you please........
Can this nifty code you provided be modified to work only on the visible cells in an Autofiltered display? TIA Vaya con Dios, Chuck, CABGx3 "Nigel" wrote: Try this.......I set it to process Sheet1, change as required. Sub Shader() Dim c As Range, xc As Integer xc = 15 With Sheets("Sheet1") For Each c In .Range("G11:G" & .Cells(.Rows.Count, "G").End(xlUp).Row) ' toggle fill If c.Value < c.Offset(-1, 0).Value Then If xc = 0 Then xc = 15 Else xc = 0 End If ' set fill With .Rows(c.Row).EntireRow.Interior .Pattern = xlSolid .ColorIndex = xc End With Next c End With End Sub -- Regards, Nigel "CLR" wrote in message ... Hi All..... I would like to alternate the row color from gray to none, for each group of rows according to the value in the cells in column G. That is, starting with row 11, I want no background color, then if G12=G11, likewise row 12 would have no color, but if G12<G11, then the background color of row 12 would switch to gray. Likewise progressing down column G, as the values change from the one above it, so does the row color, if the values are the same as the one above, the row color would stay the same as the one above. The entire column is sorted in order so there would be no like values showing up out of order. The length of column G will be unknown. Example row Col G Color 11 aa none 12 aa none 13 bb gray 14 cc none 15 cc none 16 cc none 17 dd gray 18 dd gray 19 ee none TIA Vaya con Dios, Chuck, CABGx3 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Color rows by "group"
In its current form No. This would a a more complex requirement for a
number of reasons 1. The testing of the previous row would no longer be a valid test, in other words the offset would be variable based on the last visible row. 2. The current process scans the entire range in column G, it would need to skip hidden rows, that is those that are filtered 3. Also need to consider changes to autofilter settings to recompute the shading dynamically. There is a test to check if a row is hidden (filtered) ...... c.EntireRow.Hidden ; where c is a range object representing a single cell. Process would look like this..... Store first non-hidden row test value in a variable Check if next non-hidden row is different, toggle color index and update the test value var - if different Fill non-hidden row with the color index setting My first attempt........you could arrange for this to be run whenever a new filter setting was made, it does not clear the fills for hidden rows! Sub Shader2() Dim c As Range, cTest, xc As Integer xc = 0: cTest = "" With Sheets("Sheet1") For Each c In .Range("G11:G" & .Cells(.Rows.Count, "G").End(xlUp).Row) If Not c.EntireRow.Hidden Then ' get first non hidden row test value If cTest = "" Then cTest = c.Value ' toggle fill If c.Value < cTest Then If xc = 0 Then xc = 15 Else xc = 0 cTest = c.Value End If ' set fill With .Rows(c.Row).EntireRow.Interior .Pattern = xlSolid .ColorIndex = xc End With End If Next c End With -- Regards, Nigel "CLR" wrote in message ... Now a follow on question, if you please........ Can this nifty code you provided be modified to work only on the visible cells in an Autofiltered display? TIA Vaya con Dios, Chuck, CABGx3 "Nigel" wrote: Try this.......I set it to process Sheet1, change as required. Sub Shader() Dim c As Range, xc As Integer xc = 15 With Sheets("Sheet1") For Each c In .Range("G11:G" & .Cells(.Rows.Count, "G").End(xlUp).Row) ' toggle fill If c.Value < c.Offset(-1, 0).Value Then If xc = 0 Then xc = 15 Else xc = 0 End If ' set fill With .Rows(c.Row).EntireRow.Interior .Pattern = xlSolid .ColorIndex = xc End With Next c End With End Sub -- Regards, Nigel "CLR" wrote in message ... Hi All..... I would like to alternate the row color from gray to none, for each group of rows according to the value in the cells in column G. That is, starting with row 11, I want no background color, then if G12=G11, likewise row 12 would have no color, but if G12<G11, then the background color of row 12 would switch to gray. Likewise progressing down column G, as the values change from the one above it, so does the row color, if the values are the same as the one above, the row color would stay the same as the one above. The entire column is sorted in order so there would be no like values showing up out of order. The length of column G will be unknown. Example row Col G Color 11 aa none 12 aa none 13 bb gray 14 cc none 15 cc none 16 cc none 17 dd gray 18 dd gray 19 ee none TIA Vaya con Dios, Chuck, CABGx3 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Color rows by "group"
ABSOLUTELY AMAZING NIGEL!!!!!!
Your new code works both with the Autofilter on, and with it off.....double bonus! Your extreme talent and kindness has made my day. Thanks again, ever so much. Vaya con Dios, Chuck, CABGx3 "Nigel" wrote: In its current form No. This would a a more complex requirement for a number of reasons 1. The testing of the previous row would no longer be a valid test, in other words the offset would be variable based on the last visible row. 2. The current process scans the entire range in column G, it would need to skip hidden rows, that is those that are filtered 3. Also need to consider changes to autofilter settings to recompute the shading dynamically. There is a test to check if a row is hidden (filtered) ...... c.EntireRow.Hidden ; where c is a range object representing a single cell. Process would look like this..... Store first non-hidden row test value in a variable Check if next non-hidden row is different, toggle color index and update the test value var - if different Fill non-hidden row with the color index setting My first attempt........you could arrange for this to be run whenever a new filter setting was made, it does not clear the fills for hidden rows! Sub Shader2() Dim c As Range, cTest, xc As Integer xc = 0: cTest = "" With Sheets("Sheet1") For Each c In .Range("G11:G" & .Cells(.Rows.Count, "G").End(xlUp).Row) If Not c.EntireRow.Hidden Then ' get first non hidden row test value If cTest = "" Then cTest = c.Value ' toggle fill If c.Value < cTest Then If xc = 0 Then xc = 15 Else xc = 0 cTest = c.Value End If ' set fill With .Rows(c.Row).EntireRow.Interior .Pattern = xlSolid .ColorIndex = xc End With End If Next c End With -- Regards, Nigel "CLR" wrote in message ... Now a follow on question, if you please........ Can this nifty code you provided be modified to work only on the visible cells in an Autofiltered display? TIA Vaya con Dios, Chuck, CABGx3 "Nigel" wrote: Try this.......I set it to process Sheet1, change as required. Sub Shader() Dim c As Range, xc As Integer xc = 15 With Sheets("Sheet1") For Each c In .Range("G11:G" & .Cells(.Rows.Count, "G").End(xlUp).Row) ' toggle fill If c.Value < c.Offset(-1, 0).Value Then If xc = 0 Then xc = 15 Else xc = 0 End If ' set fill With .Rows(c.Row).EntireRow.Interior .Pattern = xlSolid .ColorIndex = xc End With Next c End With End Sub -- Regards, Nigel "CLR" wrote in message ... Hi All..... I would like to alternate the row color from gray to none, for each group of rows according to the value in the cells in column G. That is, starting with row 11, I want no background color, then if G12=G11, likewise row 12 would have no color, but if G12<G11, then the background color of row 12 would switch to gray. Likewise progressing down column G, as the values change from the one above it, so does the row color, if the values are the same as the one above, the row color would stay the same as the one above. The entire column is sorted in order so there would be no like values showing up out of order. The length of column G will be unknown. Example row Col G Color 11 aa none 12 aa none 13 bb gray 14 cc none 15 cc none 16 cc none 17 dd gray 18 dd gray 19 ee none TIA Vaya con Dios, Chuck, CABGx3 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help displaying "Maximum" or "Minimum" if cell is max or min in a group? | Excel Discussion (Misc queries) | |||
How do i enable "Group" & "Ungroup" in a protected sheet | Excel Discussion (Misc queries) | |||
How do I write a conditional "IF" statement to color a group of ce | Excel Discussion (Misc queries) | |||
How do i enable "Group" & "Ungroup" in a protected sheet | Excel Discussion (Misc queries) | |||
Pivot table "Group and Show Details" vs. "SubTotals" | Excel Programming |