Conditional Formatting based on specific text in a column and it's related values in another column

[mizzoh]

Let's say I have a dropdown with two options (A2):
Category 1
Category 2

I also have a range of cells given with different phrases: (A4:C5)

Phrase A	Phrase B	Phrase C
Phrase D	Phrase E	Phrase F

and also a table which looks like this: (A6:B12)

Phrase	Category
Phrase A	Category 1
Phrase B	Category 1
Phrase C	Category 2
Phrase D	Category 1
Phrase E	Category 2
Phrase F	Category 2

What I want to do is to highlight my range of cells (A4:C5) based on the Dropdown value which I selected.

So, if I select Category 1 in the Dropdown the cells Phrase A, Phrase B and Phrase D should be highlighted with another color in my range of cells (A4:C5) not in the table itself.

Could you please explain how I can achieve this goal?

 

[etaf]

try
=COUNTIFS($A$7:$A$12,A4,$B$7:$B$12,$A$2)

for 2007, 2010 , 2013 , 2016 , 2019 or 365 Subscription excel version
Conditional Formatting

Highlight applicable range >>
A4:C5 - Change, reduce or extend the rows to meet your data range of rows

Home Tab >> Styles >> Conditional Formatting
New Rule >> Use a formula to determine which cells to format
Edit the Rule Description: Format values where this formula is true:

=COUNTIFS($A$7:$A$12,A4,$B$7:$B$12,$A$2)

Format [Number, Font, Border, Fill]
choose the format you would like to apply when the condition is true
OK >> OK

Book4
	A	B	C	D	E	F	G	H	I
1
2	category 1
3
4	Phrase A	Phrase B	Phrase C		1	1	0		category 1
5	Phrase D	Phrase E	Phrase F		1	0	0		category 2
6	Phrase	Category
7	Phrase A	Category 1
8	Phrase B	Category 1
9	Phrase C	Category 2
10	Phrase D	Category 1
11	Phrase E	Category 2
12	Phrase F	Category 2
Sheet1

Cell Formulas
Range	Formula
E4:G5	E4	=COUNTIFS($A$7:$A$12,A4,$B$7:$B$12,$A$2)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A4:C5	Expression	=COUNTIFS($A$7:$A$12,A4,$B$7:$B$12,$A$2)	text	NO

Cells with Data Validation
Cell	Allow	Criteria
A2	List	=$I$4:$I$5

will only be on the dropbox link for a few days

Cond-Frmt-ETAF.xlsx

Shared with Dropbox

www.dropbox.com

 

[mizzoh]

Thanks for your response. It works, however, I just don't understand why the criteria for the first Countifs- condition is only A4 and not the cell range of the phrases in a "OR" manner.

 

[etaf]

because conditional formatting changes the cell reference itself

So as you have selected A4 to C5 to conditional format
then it starts by k=looking at A4 as per the formula (which is why its important to get the formula cell reference correct)
=COUNTIFS($A$7:$A$12,A4,$B$7:$B$12,$A$2)

Now all the other cells are fixed with $
so then it moves to B4 and checks - effectively
=COUNTIFS($A$7:$A$12,B4,$B$7:$B$12,$A$2)
then
=COUNTIFS($A$7:$A$12,C4,$B$7:$B$12,$A$2)
then
=COUNTIFS($A$7:$A$12,A5,$B$7:$B$12,$A$2)
etc

so you only need to specify the 1st formula and conditional formatting will do the rest
Hence why its important to have the correct cell values in a formula compared to the area you select to apply the conditional formatting too

hope that makes sense