Conditional formatting based on the cell with or without formula

[itsrich]

I am using Excel as a BOM. Some cells contain text others, this formula =IFERROR(VLOOKUP(A85,'P:\Fishbowl\FishBOM\[FishLINE.xlsx]FishLINE'!A:I,9,FALSE),"") The formula returns text from the DB. The cell should be in two states, text or a formula that returns text.

The conditional format I wish is:

Text, format the cell red.
Formula that returns text, format the cell blue.

My predecessor did me no favors in how he set things up.

TY for your knowledge!

 

[etaf]

2 rules
=ISFORMULA(A1)
blue
=and(a1<>"",NOT(ISFORMULA(A1)))
red
stops blank cells highlighting

Book7
	A
1	a
2	b
3	c
4	cellc
5
6
7
8
9
Sheet1

Cell Formulas
Range	Formula
A4	A4	=C4

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A1:A9	Expression	=AND(A1<>"",NOT(ISFORMULA(A1)))	text	NO
A1:A8	Expression	=ISFORMULA(A1)	text	NO

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

Highlight applicable range >>
A2:A100 - Change, column, 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:
=ISFORMULA(A2)

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

 

[itsrich]

2 rules
=ISFORMULA(A1)
blue
=and(a1<>"",NOT(ISFORMULA(A1)))
red
stops blank cells highlighting

Book7
	A
1	a
2	b
3	c
4	cellc
5
6
7
8
9
Sheet1

Cell Formulas
Range	Formula
A4	A4	=C4

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A1:A9	Expression	=AND(A1<>"",NOT(ISFORMULA(A1)))	text	NO
A1:A8	Expression	=ISFORMULA(A1)	text	NO

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

Highlight applicable range >>
A2:A100 - Change, column, 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:
=ISFORMULA(A2)

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

etaf,

TYVM! I had not considered the third option of a blank cell. The blank cells showed up! GREAT visual information.

This is one of those things, no one else in the company will know what atef has helped me do.

As far as I am concerned you have solved the problem. Again TYVM!

 

[etaf]

You are welcome