Changing row color for each change in col A
 

I have a sheet we use to track vendors and each vendor is assigned a
cost package (budget code) in Column A. I would like to conditionally
alternate row colors for each budget code, not each line. The budget
codes are not even/odd and don't change incrementally. It would need
to work automatically so that when a row is inserted in a budget code
grouping, the row color would update. The idea is similar to the
Subtotal command in that I want alternating row colors for each change
in Column A. Thank you.

Changing row color for each change in col A
 

One way is to use an helper column, say Col B
Enter 1 in B1
Enter this in B2 and copy down till end of your data set
=IF(A2=A1,B1,IF(B1=1,2,1))

This will give you an alternating series of 1s and 2s.
Now you can select your data range, including the helper column, and choose
Conditional Formatting...
Choose formula is
=$B1=1 for one color
and
=$B1=2 for another color

Repace B with the Col you choose
"Michael" wrote:

I have a sheet we use to track vendors and each vendor is assigned a
cost package (budget code) in Column A. I would like to conditionally
alternate row colors for each budget code, not each line. The budget
codes are not even/odd and don't change incrementally. It would need
to work automatically so that when a row is inserted in a budget code
grouping, the row color would update. The idea is similar to the
Subtotal command in that I want alternating row colors for each change
in Column A. Thank you.

Changing row color for each change in col A
 

Thanks but I was hoping to do it automatically so the user wouldn't
have to copy down a formula. I suppose I could use an Insert Row
event to copy Col B.

On Jan 21, 7:18 pm, Sheeloo <="to" & CHAR(95) & "sheeloo" & CHAR(64) &
"hotmail.com" wrote:
One way is to use an helper column, say Col B
Enter 1 in B1
Enter this in B2 and copy down till end of your data set
=IF(A2=A1,B1,IF(B1=1,2,1))

This will give you an alternating series of 1s and 2s.
Now you can select your data range, including the helper column, and choose
Conditional Formatting...
Choose formula is
=$B1=1 for one color
and
=$B1=2 for another color

Repace B with the Col you choose

"Michael" wrote:
I have a sheet we use to track vendors and each vendor is assigned a
cost package (budget code) in Column A. I would like to conditionally
alternate row colors for each budget code, not each line. The budget
codes are not even/odd and don't change incrementally. It would need
to work automatically so that when a row is inserted in a budget code
grouping, the row color would update. The idea is similar to the
Subtotal command in that I want alternating row colors for each change
in Column A. Thank you.

Changing row color for each change in col A
 

Glad to send along the trial version of my "Shade Data Rows" Excel add-in.
Select the data and with a couple of clicks the selection or the entire
sheet is shaded "by value" in alternating colors of your choice.
Can also shade alternating rows or alternating groups of n rows.
Send an email to james.coneXXX at comcast.netXXX (remove the xxx).
Please include your real name and geographic location.
--
Jim Cone
Portland, Oregon USA



"Michael"

wrote in message
Thanks but I was hoping to do it automatically so the user wouldn't
have to copy down a formula. I suppose I could use an Insert Row
event to copy Col B.