Alternate row shade by change in key field

[biddlea]

I have several columns of data and want to alternate the row shad
whenever the contents of the first column changes. For example, row
1-3 refer to item 1, 4-5 to item 2, and 6-10 to item 3, I would lik
rows 1-3 shaded gray, 4-5 white, and 6-10 gray, and so on.

Before I write one, does anyone know if Excel has a built in functio
for this? Creating logical groups using shading seems basic enough tha
I must be missing something.

Thanks

 

[Frank Kabel]

Hi
try the following:
- Assumption: your item ID is in column A
- select your rows of interest (assumption: you start in row 1)
- goto 'Format - Conditional Format'
- enter the following formula:
=MOD(SUMPRODUCT(($A$1:$A1<>"")/COUNTIF($A$1:$A1,$A$1:$A1&"")),2)
- choose your gray pattern

 

[RagDyer]

For built in choices, try:

<Format> <AutoFormat>
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================

I have several columns of data and want to alternate the row shade
whenever the contents of the first column changes. For example, rows
1-3 refer to item 1, 4-5 to item 2, and 6-10 to item 3, I would like
rows 1-3 shaded gray, 4-5 white, and 6-10 gray, and so on.

Before I write one, does anyone know if Excel has a built in function
for this? Creating logical groups using shading seems basic enough that
I must be missing something.

Thanks!

 

[biddlea]

Since the first column is sometimes not numeric I used the formula belo
to fill column H. H is a 0 or a 1 and only changes when A does. Then
used the selective format based on H.

=IF(A1=A2,H1,MOD(H1+1,2))

Of course yesterday didn't know about selective formatting and cam
close to throwing in some ugly VBA code. Thanks for the help Frank