Conditional Formatting where cell value is a formula

[RattlingCarp3048]

I have this formula D=VLOOKUP(B7,'Sheet 1'!C:E,3,0) which either returns a date or "New"

i want to conditionally format column D to highlight the cell if the value = "New"

these are all the variations i have tried so far and nothing has worked. Any help is appreciated.

=ISFORMULA($D:$D)
=ISFORMULA($D:$D)="New"
=ISFORMULA(indirect($D:$D))="New"
=ISFORMULA(INDIRECT($D:$D)="New")
=ISFORMULA(INDIRECT($D:$D="New"))
=INDIRECT($D:$D="New")
=INDIRECT($D:$D)="New"

*also tried putting the vlookup directly into the conditional formatting formula field as well as New vs "New"

 

[Joe4]

When you write Conditional Formatting formulas, if applying to a multi-cell range, you write the formula as it applies to the very FIRST cell in your selection (a single cell), and Excel will automatically adjust it for the rest of the cells in that range.

So, if you wanted to Conditionally Format column D (though I would recommend against formatting an entire column, as that is over 1 million cells), you would select column D, and then enter your formula like this:

=ISFORMULA($D1)

(notice how it is referencing a single cell, not the entire range).

 

[RattlingCarp3048]

I can limit the range to D1:D30, no problem, no need to go to 1M in this case.

So how would I limit the formatting to only the cells that =New?

Isformula($D1="New")?

 

[awoohaw]

try: =AND(ISFORMULA($D1),$D1="New")

 

[awoohaw]

I omitted the $, but it makes no difference in a single column.

Mr Excel Questions.xlsx
	A	B	C	D	E	F	G
2			A	456	1/15/2023
3			B	14132	1/16/2023
4			C	12	1/17/2023
5
6		B7		LOOKUP
7		D		New
8		B		1/16/2023
9		C		1/17/2023
10		A		1/15/2023
11		Q		New
12
Sheet3

Cell Formulas
Range	Formula
D7:D11	D7	=IF(ISNA(VLOOKUP(B7,$C$2:$E$4,3,0)),"New",VLOOKUP(B7,$C$2:$E$4,3,0))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D7:D11	Expression	=AND(ISFORMULA(D7),D7="New")	text	NO

 

[RattlingCarp3048]

try: =AND(ISFORMULA($D1),$D1="New")

this is the one that ended up working for me, without the $. Thank you for getting me in the right direction!

 

[awoohaw]

@RattlingCarp3048 , if you find that one of the threads has provided you with an answer please mark that thread as answered.

 

[RattlingCarp3048]

@RattlingCarp3048 , if you find that one of the threads has provided you with an answer please mark that thread as answered.

I marked #4 as the answer on Wednesday. Can you not see it in your end?

 

[awoohaw]

I missed it. Thanks!

 

[RattlingCarp3048]

I missed it. Thanks!

You're welcome