Conditional Formatting based on Named Range (List)

[zero269]

Hello,

I'm having some trouble combining two Conditional Formatting rules to work across two columns instead of using them individually.

Currently, I'm using the following to highlight the cells in the Author and Series columns based on a named range (lstPreferred):

Used in Author column (D)

=SUM(COUNTIF($A2,"*"&lstPreferred&"*"))

Used in Series column (E)

=SUM(COUNTIF($B2,"*"&lstPreferred&"*"))

NOTE: I just discovered while drawing up this post that the xl2bb Add-in is not working right now. I'll need to troubleshoot this later...

In the meantime, here's my Sample Sheet that I'm using to TRY and combine the two to work independently... if possible:

I tried using the following in the Combined (test) column F to no avail:

=SUM(COUNTIFS($A2,"*"&lstPreferred&"*",$B2,"*"&lstPreferred&"*"))

I've tried playing around with the OR function but had undesired results as I couldn't figure it out.

Any help with on this would be greatly appreciated.

Thank you,

 

[Fluff]

Try select both columns & then use

=SUM(COUNTIF(A2,"*"&lstPreferred&"*"))

 

[zero269]

Try select both columns & then use

=SUM(COUNTIF(A2,"*"&lstPreferred&"*"))

Amazing Fluff!

Worked perfectly!

I can't believe I didn't think of removing the absolute column reference. It never crossed my mind.

Thanks for your help with this:

I got my XL2BB add-in working again, so here are the results:

VBA Testing.xlsm
	A	B
1	Author	Series
2	Dr. Seuss	Berenstain Bears
3	Larrison, Joanne
4	Lucado, Max
5	Mayer, Mercer	Corduroy
6	Rey, Margret	Zonderkidz
7	Sendak, Maurice	Dr. Seuss
8	Willems, Mo
CF List

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:B8	Expression	=SUM(COUNTIF(A2,"*"&lstPreferred&"*"))	text	NO

Applies to: =$A$2:$B$8 is how the Conditional Formatting Rules Manager displays

 

[Fluff]

Glad to help & thanks for the feedback.