Conditional Format to determine if cell is text or formula

[StewartS]

Hi

I have a table which one column can either be a vlookup formula or it will be possible for the user to overwrite a text value as well. In both cases the value comes from a data validation list. The data comes from a download of jobs which could be active, completed or rejected. So in principle the user could change an active job to completed and overwrite the formula.

I was wondering whether it is possible to identify with conditional formatting if the cell contains the vlookup formula or if it has been overwritten with a text value.

Thanks in Advance

StewartS

 

[Special-K99]

Re: Conditional Format to determine if cell is texat or formula

If youre using Excel 2013 you can use ISFORMULA()

https://support.office.com/en-us/Ar...3839bfd6b1e5?ui=en-US&rs=en-US&ad=US&fromAR=1

 

[Snakehips]

Re: Conditional Format to determine if cell is texat or formula

Stewart,

Maybe like...

Excel Workbook
	B
2	By Formula
3	Overwritten
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B2	1. / Formula is =CELL("prefix",B2)="'"	Abc
B3	1. / Formula is =CELL("prefix",B2)="'"	Abc

Hope that helps.

 

[BQardi]

Re: Conditional Format to determine if cell is texat or formula

If before 2013, you can make your own UDF...

Put in a code module:

Function ISFORMULA(Rng As Range)
ISFORMULA = Rng.HasFormula
End Function

Usage: just use like any other formula in Excel.
=ISFORMULA(A1)

With Conditional Formatting:
Select range that needs to be checked for cells that are not formulas

• Conditional Formatting
• Use formula...
• Put this formula in: =NOT(ISFORMULA(A1))
  (Use the top left cell address of the selected range)
• Choose formatting

(Note, in response to Snakehips solution, if the user enters pure numbers, the formula: =CELL("prefix",B2)="'" would return FALSE, but as you said: "if it has been overwritten with a text value", this probably won't happen, and the formula is then useful, simple and won't require macros as long as the user only overwrites with text)

 

[StewartS]

Re: Conditional Format to determine if cell is texat or formula

Hi thanks for the replies unfortunately I'm still on Excel 2010 but Snakehips details work although I changed the 1. / Formula is =CELL("prefix",B2)="'" to
1. / Formula is =CELL("prefix",B2)<>"'"

StewartS

 

[StewartS]

Re: Conditional Format to determine if cell is texat or formula

Thanks BQardi

I like this solution as it works if the user selects a blank value which is a possible result and as the workbook has some macros in it already adding the function in won't be a problem.

StewartS

 

[BQardi]

Re: Conditional Format to determine if cell is texat or formula

You're welcome...