alternate Formatting on multiple rows

**bimo** · 06-21-2013, 06:05 AM

hello

I would like have dark & lighter grey Fill to alternate on rows, as Column E change..

is there way of doing it other than manually ?

Thanks

**JBeaucaire** · 06-21-2013, 06:10 AM

1) Format the entire data set to the lighter grey color

2) Apply conditional formatting to the entire data set using this formula, set it to the darker color:

=ISODD(SUM(IF(FREQUENCY($E$1:$E1,$E$1:$E1)>0,1)))

**benishiryo** · 06-21-2013, 06:17 AM

hi bimo, another alternative:
=MOD(SUMPRODUCT(($E$1:$E1<>"")/COUNTIF($E$1:$E1,$E$1:$E1)),2)

**Marcol** · 06-21-2013, 07:33 AM

Originally Posted by benishiryo

hi bimo, another alternative:
=MOD(SUMPRODUCT(($E$1:$E1<>"")/COUNTIF($E$1:$E1,$E$1:$E1)),2)

You might have to wrap that in ROUND()

Formula:

Please Login or Register  to view this content.

[EDIT]
Because all C/F formulae are treated as arrays, you could use

Formula:

Please Login or Register  to view this content.

But Jerrys' solution is the shortest way. ...

**taps** · 06-21-2013, 07:41 AM

Hi,

Another Alternatives

=IF(MOD(ROW(),2)=0,TRUE,FALSE) 'Put this in one condition
=IF(MOD(ROW(),2)=1,TRUE,FALSE) 'Put this in another condition

regards
taps

Again:: Sorry, this is for each alternative rows.

**bimo** · 06-21-2013, 09:11 AM

Hi Guy, none of the fourmlas worked for me

..I can`t identify where I went wrong..Do you mind uploading the worksheet with the forumla working ? sorry for the stupid question.

**JBeaucaire** · 06-21-2013, 10:06 AM

Like so....

**Marcol** · 06-21-2013, 10:08 AM

Here you go, two sheets one for Jerrys' way, the other Bens'

[EDIT]
Jerry beat me to it, didn't see he was on line ...

I have cleared the fill colour from the range, just select A1:A45 and fill with the colour of your choice.

Better this than using two C/F conditions.

Conditional Formatting is volatile by definition.
It can be very expensive on larger sheets.
So the less conditions the better.