Format cell in Excel if contents are a number or a function

Question

I understand conditional formatting but I don't see how it can help me. I'm looking at a sheet with many product entries and for most of them I get values from a table (vlookup) while for others I punch them in.

For example:

• Cell b2 contents are =vlookup(a2,range,2,false) and what is displayed is 0.6

• Cell b3 I know vlookup will fail (there is no data for the product in the range for a3) so I manually put in 0.8.

With over a hundred lines like this how can I program a highlight/Format condition to visually distinguish between those that were introduced manually from those retrieved with vlookup?

Answer 1

With the aid of a UDF (user defined function) you can use Conditional Formatting:

Function IsFormula(r As Range) As Boolean
    IsFormula = r.HasFormula
End Function

And then use IsFormula in the format condition