CF Rule: Highlight Range using Multiple Criteria

[zero269]

Hello,

I'm currently using the following CF Rule to highlight cells in two columns that meet a single criteria from a list on another sheet.

=SUM(COUNTIF(C2,"*"&INDIRECT("t_Whitelist[Author-Series]")&"*"))

However, I would like to expand my criteria to only highlight the cells that meet the current criteria AND one more.

For example, instead of updating a single list with preferred Author's and Series, I added a Status column that will use "active" as the second criteria. This way I can make changes to the Whitelist without having to remove them, only to add them back as needed.

In other words, if Status = "active", AND my current criteria, then highlight cell.

I've tried replacing COUNTIF with COUNTIFS but get a VALUE error.

VBA Testing.xlsm
	A	B	C	D	E	F	G	H
1	Quiz	Title	Author	Series		Author-Series	Range	Status
2	149101	All You Ever Need	Lucado, Max			Abdo, Kenny	Author	active
3	88934	Aloha, Scooby-Doo!	Weyn, Suzanne	Scooby-Doo (Scholastic);		Aesop	Series
4	192174	Ant and the Grasshopper	Windmill Book Editors	Let's Learn Aesop's Fables;		All Aboard America	Series	active
5	9004	Bear Detectives	Berenstain, Stan	Berenstain Bears;		Amazing Science	Series	active
6	507949	Black Panthers	Abdo, Kenny	Superhero Animals;		Berenstain Bears	Series
7	59946	Gateway Arch	Murray, Julie	All Aboard America;		Lucado, Max	Author	active
8	103786	Tires, Spokes, and Sprockets	Dahl, Michael	Amazing Science: Simple Machines;		Scooby-Doo	Series
CF_Range

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C2:D8	Expression	=SUM(COUNTIF(C2,"*"&INDIRECT("t_Whitelist[Author-Series]")&"*"))	text	NO

Any help would be greatly appreciated.

 

[awoohaw]

I think you just need to put an AND function and test the cells in H2. =AND( SUM(COUNTIF(C2,"*"&INDIRECT("t_Whitelist[Author-Series]")&"*")), H2="active" )

(if the case can change, you may want to be doubly sure you're matching, and maybe use UPPER(H2)="ACTIVE"

 

[Fluff]

How about

=SUM(COUNTIF(C2,"*"&FILTER(INDIRECT("t_Whitelist[Author-Series]"),INDIRECT("t_whitelist[Status]")="active")&"*"))

 

[zero269]

I think you just need to put an AND function and test the cells in H2. =AND( SUM(COUNTIF(C2,"*"&INDIRECT("t_Whitelist[Author-Series]")&"*")), H2="active" )

(if the case can change, you may want to be doubly sure you're matching, and maybe use UPPER(H2)="ACTIVE"

Hello awoohaw,

Sorry for the late reply. I tested your code, but unfortunately it's only highlighting one cell out of the four that are active. Here's what it looks like:

VBA Testing.xlsm
	A	B	C	D	E	F	G
1	Quiz	Title	Author	Series		Author-Series	Status
2	149101	All You Ever Need	Lucado, Max			Abdo, Kenny	active
3	88934	Aloha, Scooby-Doo!	Weyn, Suzanne	Scooby-Doo (Scholastic);		Aesop
4	192174	Ant and the Grasshopper	Windmill Book Editors	Let's Learn Aesop's Fables;		All Aboard America	active
5	9004	Bear Detectives	Berenstain, Stan	Berenstain Bears;		Amazing Science	active
6	507949	Black Panthers	Abdo, Kenny	Superhero Animals;		Berenstain Bears
7	59946	Gateway Arch	Murray, Julie	All Aboard America;		Lucado, Max	active
8	103786	Tires, Spokes, and Sprockets	Dahl, Michael	Amazing Science: Simple Machines;		Scooby-Doo
9	t_Books					t_Whitelist
CF_Range

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C2:C8	Expression	=AND( SUM(COUNTIF(C2,"*"&INDIRECT("t_Whitelist[Author-Series]")&"*")), G2="active" )	text	NO

One thing I learned after playing around with some changes to your code is that AND & INDIRECT don't really play well together.
Unfortunately, everything I tried would result in unexpected results. For this reason I decided to switch back to using Named Ranges for my Table references.

Thank you…

 

[zero269]

How about

=SUM(COUNTIF(C2,"*"&FILTER(INDIRECT("t_Whitelist[Author-Series]"),INDIRECT("t_whitelist[Status]")="active")&"*"))

Hello Fluff,

Thanks for helping me out again. Your code works perfectly.

Due to the amount of time I spent yesterday working on a solution, I discovered that the INDIRECT function - as much as I like it - has some flaws as it relates to performance.

I decided to switch back to using Named Ranges for my large datasets which is where your formula will be used. So, the only changes I had to make was converting the Table references to Named Ranges.

=SUM(COUNTIF(C2,"*"&FILTER(l_AuthorSeries,l_WhitelistStatus="active")&"*"))

VBA Testing.xlsm
	A	B	C	D	E	F	G
1	Quiz	Title	Author	Series		Author-Series	Status
2	149101	All You Ever Need	Lucado, Max			Abdo, Kenny	active
3	88934	Aloha, Scooby-Doo!	Weyn, Suzanne	Scooby-Doo (Scholastic);		Aesop
4	192174	Ant and the Grasshopper	Windmill Book Editors	Let's Learn Aesop's Fables;		All Aboard America	active
5	9004	Bear Detectives	Berenstain, Stan	Berenstain Bears;		Amazing Science	active
6	507949	Black Panthers	Abdo, Kenny	Superhero Animals;		Berenstain Bears
7	59946	Gateway Arch	Murray, Julie	All Aboard America;		Lucado, Max	active
8	103786	Tires, Spokes, and Sprockets	Dahl, Michael	Amazing Science: Simple Machines;		Scooby-Doo
9	t_Books					t_Whitelist
CF_Range

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C2:D8	Expression	=SUM(COUNTIF(C2,"*"&FILTER(l_AuthorSeries,l_WhitelistStatus="active")&"*"))	text	NO

Thanks again Fluff, and best regards. This was very helpful and means I don't have to use a separate - single column - list for the Whitelist.

 

[Fluff]

Glad we could help & thanks for the feedback.