Conditional Formatting based on names in a list AND.

[amoverton2]

Aloha All,

So I found this thread: Conditional Formatting based on names in a list.

This works but I'd like to add to it. What about a name in a list and another cell that has anything in it (date, words, numbers, etc) that is directly to the right of it.

For example, based on my sheet, if I type any name in cells A2 thru A25 (or however long it may go), I want to conditional format if that name appears in the list of names from D2:D15 and if there is a date next to the name in the list from D2:D15 (in the normal spreadsheet the list of names in on another sheet but I condensed it down here to get the formula right) If that name is in the list but no date next to it, it will not conditional format.

My sheet below is with a formula I was playing around but it does not work because as you can see both Dan, Dexter, and Crystal are formatted but they do not have a date in the Date of Training column.

If this takes multiple columns or formulas I'm cool with that, as well as a macro. I appreciate the help, Mahalo!

Book1
	A	B	C	D	E
1	Name			Name	Date of Training
2	Pistol			Pistol	1-Jun-21
3	Adam			Patrick	2-Mar-21
4	Dan			Joe	3-Jun-21
5	Dexter			Dan
6	Jen			Bob	18-Feb-21
7	Crystal			Phil	21-Jan-21
8				Carey	7-Jun-21
9				Lucy	25-Mar-21
10				Dexter
11				Adam	15-Apr-21
12				Jen
13				Mark	8-May-21
14				Matt	8-May-21
15				Crystal
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:A25	Expression	=AND(COUNTIF(D2:D15,A2)>0,$E1<>"")	text	NO

 

[jasonb75]

Try it with

=COUNTIFS($D$2:$D$15,$A2,$E$2:$E$15,">0")

 

[Fluff]

Try changing your CF formula to

=AND(A2<>"",COUNTIFS($D$2:$D$15,A2,$E$2:$E$15,"<>"))