Macro for formatting grouped rows?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

mike.goldman@mac.com - 05 Sep 2007 19:26 GMT

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?

Reply to this Message

JE McGimpsey - 05 Sep 2007 21:20 GMT

> I've been playing with conditional formatting and it's not
> accomplishing my task. That is, to apply a gray color band to
[quoted text clipped - 16 lines]
>
> 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>

Reply to this Message

mike.goldman@mac.com - 05 Sep 2007 21:45 GMT

> In article <1189016808.197485.273...@w3g2000hsg.googlegroups.com>,
>
[quoted text clipped - 29 lines]
> =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.

Reply to this Message

Mac Forum / Applications / Excel / September 2007

Macro for formatting grouped rows?