conditional formating adjacent rows

[mydisplayname]

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

 

[T. Valko]

Try this...

I have sheet with some numbers in rows A1:A10

Select the range A1:A10
Goto the menu Format>Conditional 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

 

[Pete_UK]

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

 

[T. Valko]

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


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

 

[Pete_UK]

Yes, that seemed to work - well done !

Pete

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


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





- Show quoted text -