Macro for formatting grouped rows?

[mike.goldman]

I've been playing with conditional formatting and it's not
accomplishing my task. That is, to apply a gray color band to
alternating groups similar rows in a large excel file. For example, if
all other data in the rows were different, and column E had similar
data, like this:

1
1
3
3
3
3
3
5
6
6
6
6

I'd want all the rows with E=1, E=5, etc. to be gray. Is this possible?

 

[JE McGimpsey]

I've been playing with conditional formatting and it's not
accomplishing my task. That is, to apply a gray color band to
alternating groups similar rows in a large excel file. For example, if
all other data in the rows were different, and column E had similar
data, like this:

1
1
3
3
3
3
3
5
6
6
6
6

I'd want all the rows with E=1, E=5, etc. to be gray. Is this possible?

One way:

Select the sheet (or the desired rows), with any cell in Row 1 active.

Choose Format/Conditional Formatting...

CF1: Formula is
=MOD(ROUND(SUMPRODUCT(1/COUNTIF($E$1:$E1,$E$1:$E1)),0),2)=1
Format1: <pattern>/<gray>

 

[mike.goldman]

One way:

Select the sheet (or the desired rows), with any cell in Row 1 active.

Choose Format/Conditional Formatting...

CF1: Formula is
=MOD(ROUND(SUMPRODUCT(1/COUNTIF($E$1:$E1,$E$1:$E1)),0),2)=1
Format1: <pattern>/<gray>

Genius. Works like a charm. It amazes me how you guys come up with
this stuff. Thanks much.