Conditional Formatting based on Conditional Formatting

[kohalloran]

This may seem like a simple question, but I can't seem to figure it out. I have cells in a table that I want to color based on a drop down box. For example, if I have chosen "Kindergarten" from the drop down box, I want the cells that say everything kindergarten related to turn blue (more specifically, I want the cell that says "math skills" "story time" and "computer skills" to turn blue. I don't want to choose the blue cells by cell number, as the rows will change frequently.

Is there a way to do that? I'm using Excel 2007.

 

[Akashwani]

Hi, Welcome to MrExcel.

Could you provide some sample data? Something like this perhaps....

Excel Workbook
	A	B	C	D	E	F	G	H	I
1	Kindergarden	Data1	Data2	Data3	*	Kindergarden	Math Skills	Story Time	Computer Skills
2	*	Math Skills	Computer Time	Dog	*	*	*	*	*
3	*	Cat	Math Test	Story Time	*	*	*	*	*
4	*	tea time	Pig	Bull	*	*	*	*	*
5	*	Mouse	Computer Skills	Story skills	*	*	*	*	*
Sheet5

The formula used in Conditional Formatting to fill the cells blue is....

=AND($A$1=$F$1,OR(B2=$G$1,B2=$H$1,B2=$I$1))

I hope that helps.

Ak

 

[T. Valko]

Hi, Welcome to MrExcel.

Could you provide some sample data? Something like this perhaps....

Excel Workbook
	A	B	C	D	E	F	G	H	I
1	Kindergarden	Data1	Data2	Data3	*	Kindergarden	Math Skills	Story Time	Computer Skills
2	*	Math Skills	Computer Time	Dog	*	*	*	*	*
3	*	Cat	Math Test	Story Time	*	*	*	*	*
4	*	tea time	Pig	Bull	*	*	*	*	*
5	*	Mouse	Computer Skills	Story skills	*	*	*	*	*
Sheet5

The formula used in Conditional Formatting to fill the cells blue is....

=AND($A$1=$F$1,OR(B2=$G$1,B2=$H$1,B2=$I$1))

I hope that helps.

Ak

When you enter a formula in the conditional formatting refedit (that's what that little box is called), it's automatically evaluated as an array so you can enter array formulas in the refedit (no CSE needed, that only needs to be done on the worksheet).

=AND($A$1=$F$1,OR(B2=$G$1:$I$1))

=AND($A$1=$F$1,MATCH(B2,$G$1:$I$1,0))

 

[kohalloran]

Hmmm... but what if I want to search the entire column B for the Kindergarten words (and the entire column B is about 100 cells long)? And I want it to be able to switch so that if I choose "Grade 1-3" instead of "Kindergarten" from the drop down box in cell A1, that different (or in some cases the same) cells shade in Column B.... Like perhaps "Grade 1-3" I need "Math Skills" and "Tea Time" (regardless of where they appear in Column B), and for "Kindergarten" I need "Tea Time" and "Cat".

Thank you all for helping with this... I really do appreciate the time you are taking.

 

[Akashwani]

Hi,

You can use the formula provided by Biff and add another condition..
Use this for Kindergarden
=AND($A$1=$F$1,MATCH(B2,$G$1:$I$1,0))
Use this for Grade 1-3
=AND($A$1=$F$1,MATCH(B2,$G$2:$H$2,0))

Excel Workbook
	A	B	C	D	E	F	G	H	I
1	Grade 1-3	Data1	*	*	*	Kindergarden	Math Skills	Story Time	Computer Skills
2	*	Math Skills	*	*	*	Grade 1-3	Math Skills	Tea Time	*
3	*	Cat	*	*	*	*	*	*	*
4	*	tea time	*	*	*	*	*	*	*
5	*	Mouse	*	*	*	*	*	*	*
6	*	Computer Time	*	*	*	*	*	*	*
7	*	Math Test	*	*	*	*	*	*	*
8	*	Pig	*	*	*	*	*	*	*
9	*	Computer Skills	*	*	*	*	*	*	*
10	*	Dog	*	*	*	*	*	*	*
11	*	Story Time	*	*	*	*	*	*	*
12	*	Bull	*	*	*	*	*	*	*
13	*	Story skills	*	*	*	*	*	*	*
14	*	tea time	*	*	*	*	*	*	*
Sheet5

Excel Workbook
	A	B	C	D	E	F	G	H	I
1	Kindergarden	Data1	*	*	*	Kindergarden	Math Skills	Story Time	Computer Skills
2	*	Math Skills	*	*	*	Grade 1-3	Math Skills	Tea Time	*
3	*	Cat	*	*	*	*	*	*	*
4	*	tea time	*	*	*	*	*	*	*
5	*	Mouse	*	*	*	*	*	*	*
6	*	Computer Time	*	*	*	*	*	*	*
7	*	Math Test	*	*	*	*	*	*	*
8	*	Pig	*	*	*	*	*	*	*
9	*	Computer Skills	*	*	*	*	*	*	*
10	*	Dog	*	*	*	*	*	*	*
11	*	Story Time	*	*	*	*	*	*	*
12	*	Bull	*	*	*	*	*	*	*
13	*	Story skills	*	*	*	*	*	*	*
14	*	tea time	*	*	*	*	*	*	*
Sheet5

I hope that helps.

Ak