|
conditional formating adjacent rows
Greetings
I have sheet with some numbers in rows A1:A10 Number in adjacent rows can repeat so I want to color all rows having the same values wih two alternating colours, eg: CELL VALUE ROW COLOUR A1 1 WHITE A2 1 WHITE A3 4 GREY A4 5 WHITE A5 5 WHITE A6 5 WHITE A7 8 GREY A8 10 WHITE A9 10 WHITE A10 12 GREY i'e I want to alternatingly group and colour rows using only two colours eg grey or white. The conditional formatting should only change colours when a different value is found as rows are decended from top to bottom. I have tried this formula (=$A1=$A2) but it sort of leaves out the boundary cells Any help will be appreciated |
conditional formating adjacent rows
Try this...
I have sheet with some numbers in rows A1:A10 Select the range A1:A10 Goto the menu FormatConditional Formatting Select the Formula Is option Enter this formula in the box on the right: =COUNTIF(A$1:A$10,A1)=1 Click the Format button Select the desired style(s) OK out -- Biff Microsoft Excel MVP "mydisplayname" wrote in message ... Greetings I have sheet with some numbers in rows A1:A10 Number in adjacent rows can repeat so I want to color all rows having the same values wih two alternating colours, eg: CELL VALUE ROW COLOUR A1 1 WHITE A2 1 WHITE A3 4 GREY A4 5 WHITE A5 5 WHITE A6 5 WHITE A7 8 GREY A8 10 WHITE A9 10 WHITE A10 12 GREY i'e I want to alternatingly group and colour rows using only two colours eg grey or white. The conditional formatting should only change colours when a different value is found as rows are decended from top to bottom. I have tried this formula (=$A1=$A2) but it sort of leaves out the boundary cells Any help will be appreciated |
conditional formating adjacent rows
I don't think so, Biff. If the OP had 4 in A4, then s/he would also
want it to be grey. I think that the number of discreet numbers would need to be counted, and if odd they should be white and if even then grey, but the formula seemed too complex when I tried it earlier. Pete On Sep 8, 4:45*pm, "T. Valko" wrote: Try this... I have sheet with some numbers in rows A1:A10 Select the range A1:A10 Goto the menu FormatConditional Formatting Select the Formula Is option Enter this formula in the box on the right: =COUNTIF(A$1:A$10,A1)=1 Click the Format button Select the desired style(s) OK out -- Biff Microsoft Excel MVP "mydisplayname" wrote in message ... Greetings I have sheet with some numbers in rows A1:A10 Number in adjacent rows can repeat so I want to color all rows having the same values wih two alternating colours, eg: CELL *VALUE * * ROW COLOUR A1 * * 1 * * * * * *WHITE A2 * * 1 * * * * * *WHITE A3 * * 4 * * * * * *GREY A4 * * 5 * * * * * *WHITE A5 * * 5 * * * * * *WHITE A6 * * 5 * * * * * *WHITE A7 * * 8 * * * * * *GREY A8 * * 10 * * * * *WHITE A9 * * 10 * * * * *WHITE A10 * 12 * * * * * GREY i'e I want to alternatingly group and colour rows using only two colours eg grey or white. The conditional formatting should only change colours when a different value is found as rows are decended from top to bottom. I have tried this formula (=$A1=$A2) but it sort of leaves out the boundary cells Any help will be appreciated- Hide quoted text - - Show quoted text - |
conditional formating adjacent rows
Hmmm...
I may have (probably!) misunderstood what they want. So, try this... Select the range *A2:A10* Formula Is: =MOD(SUMPRODUCT(--(A$1:A1<A$2:A2)),2) This will slow things down if the range to format is 1000's of rows. -- Biff Microsoft Excel MVP "Pete_UK" wrote in message ... I don't think so, Biff. If the OP had 4 in A4, then s/he would also want it to be grey. I think that the number of discreet numbers would need to be counted, and if odd they should be white and if even then grey, but the formula seemed too complex when I tried it earlier. Pete On Sep 8, 4:45 pm, "T. Valko" wrote: Try this... I have sheet with some numbers in rows A1:A10 Select the range A1:A10 Goto the menu FormatConditional Formatting Select the Formula Is option Enter this formula in the box on the right: =COUNTIF(A$1:A$10,A1)=1 Click the Format button Select the desired style(s) OK out -- Biff Microsoft Excel MVP "mydisplayname" wrote in message ... Greetings I have sheet with some numbers in rows A1:A10 Number in adjacent rows can repeat so I want to color all rows having the same values wih two alternating colours, eg: CELL VALUE ROW COLOUR A1 1 WHITE A2 1 WHITE A3 4 GREY A4 5 WHITE A5 5 WHITE A6 5 WHITE A7 8 GREY A8 10 WHITE A9 10 WHITE A10 12 GREY i'e I want to alternatingly group and colour rows using only two colours eg grey or white. The conditional formatting should only change colours when a different value is found as rows are decended from top to bottom. I have tried this formula (=$A1=$A2) but it sort of leaves out the boundary cells Any help will be appreciated- Hide quoted text - - Show quoted text - |
conditional formating adjacent rows
Yes, that seemed to work - well done !
Pete On Sep 8, 5:09*pm, "T. Valko" wrote: Hmmm... I may have (probably!) misunderstood what they want. So, try this... Select the range *A2:A10* Formula Is: =MOD(SUMPRODUCT(--(A$1:A1<A$2:A2)),2) This will slow things down if the range to format is 1000's of rows. -- Biff Microsoft Excel MVP "Pete_UK" wrote in message ... I don't think so, Biff. If the OP had 4 in A4, then s/he would also want it to be grey. I think that the number of discreet numbers would need to be counted, and if odd they should be white and if even then grey, but the formula seemed too complex when I tried it earlier. Pete On Sep 8, 4:45 pm, "T. Valko" wrote: Try this... I have sheet with some numbers in rows A1:A10 Select the range A1:A10 Goto the menu FormatConditional Formatting Select the Formula Is option Enter this formula in the box on the right: =COUNTIF(A$1:A$10,A1)=1 Click the Format button Select the desired style(s) OK out -- Biff Microsoft Excel MVP "mydisplayname" wrote in message ... Greetings I have sheet with some numbers in rows A1:A10 Number in adjacent rows can repeat so I want to color all rows having the same values wih two alternating colours, eg: CELL VALUE ROW COLOUR A1 1 WHITE A2 1 WHITE A3 4 GREY A4 5 WHITE A5 5 WHITE A6 5 WHITE A7 8 GREY A8 10 WHITE A9 10 WHITE A10 12 GREY i'e I want to alternatingly group and colour rows using only two colours eg grey or white. The conditional formatting should only change colours when a different value is found as rows are decended from top to bottom. I have tried this formula (=$A1=$A2) but it sort of leaves out the boundary cells Any help will be appreciated- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
| All times are GMT +1. The time now is 07:45 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com