Change cell color if a value is manually entered instead of formula

[Ninad7]

Hi! I want to see if the value is entered manually or the the value is coming from my formula (vlookup). I want the cells where the value are entered manually to be colored red and no color when the value is coming from formula. Thank you.

 

[etaf]

you can use isformula()

=NOT(ISFORMULA(A3))

Book4
	A	B	C	D	E
1
2
3	a	1		1	a
4	b	2		2	b
5	12	3		3	c
6	d	4		4	d
7				5	e
8
Sheet2

Cell Formulas
Range	Formula
A3:A4,A6	A3	=VLOOKUP(B3,$D$3:$E$8,2,FALSE)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A3:A6	Expression	=NOT(ISFORMULA(A3))	text	NO

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

Highlight applicable range >>
B2:B100 - Change, 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:
=NOT(ISFORMULA(A2))

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

 

[Herakles]

Highlight your range you wish this to apply to, let's say from A3:W20. Go into conditional formatting and select NEW RULE | USE A FORMULA TO DETERMINE WHICH CELLS TO FORMAT.

Put in = NOT(ISFORMULA(A3)) and pick the format you want to apply.

A3 is obviously a reference to the first cell in your range but this formatting then applies to all. The result is that within your range, any cell that is NOT a formula is conditionally formatted.

 

[Ninad7]

Thank so much @etaf & @Herakles for such a clear explanation. It worked for me. Thank you so much!!

 

[etaf]

you are welcome

 

[Herakles]

Anything to encourage pretty coloured worksheets Ninad7.