Conditional Formatting for dynamic table

[greenae]

Hi, i've got a big table of data that will change. I want to add a background color to groups. More specifically, I have End-Use groups that will change and I want the formatting to detect the groups and apply a format. Is this possible? Right now I have it set to use the Mod(Row(),2)= 0 to apply the format to every other row, but I'd prefer to detect the groups... I'm doubtful that this is possible, but if it is please let me know!

THANKS!!!

 

[Brian from Maui]

Hi, i've got a big table of data that will change. I want to add a background color to groups. More specifically, I have End-Use groups that will change and I want the formatting to detect the groups and apply a format. Is this possible? Right now I have it set to use the Mod(Row(),2)= 0 to apply the format to every other row, but I'd prefer to detect the groups... I'm doubtful that this is possible, but if it is please let me know!

THANKS!!!

Care to provide what would trigger the coloring of the cells?

 

[greenae]

i'm not sure what the formula would be, but basically if B2<>B1 I want the color to change. And that would continue throughout all of Column B.

Ex.

Dog
Dog
Cat
Cow
Cow
Horse
Horse
Pig

I would want the Dog rows white, the cat row grey, the cow rows white, the horse rows grey etc....

Is this what you wanted?

 

[Brian from Maui]

You'll probably have to adjust this, but hopefully this will get you on the right track. Using Conditional Formatting and Formula Is,

=MATCH(B1,$A$2:$A$10,0)

Where A2:A10 houses your criteria list

 

[Barry Katcher]

Not quite sure what you mean by "End-use groups" but let's assume each record belongs to a certain group (let's say Group 1, Group 2 or Group 3). Use a blank column and enter the group number for that record. Then, do your conditional formatting for each row based on that column. You can get up to 4 different colors, three with the conditional formatting and the forth by using regular formatting to pre-format the entire range (blue below).

In the example below, I selected A2:D7 and first formatted all the cells with a blue background. Then, I clicked on Format>Conditional Formatting and entered these three conditions:

Condition 1
Formula is: =$D2=4, format pink pattern

Condition 2
Formula is: =$D2=3, format green background

Condition 3
Formula is: =$D2=2, format yellow background

Book1
	A	B	C	D
1	Last	First	Age	Group
2	Cramb	Geri	36	1
3	Rabbat	Elf	59	3
4	Katcher	Barry	60	3
5	Riedel	Earle	198	4
6	DuLac	Pam	45	2
7	Waterman	Joyce	42	4
Sheet1