conditional format if the cell contains a formula

[ellebelle]

I would like to colour code all the cells in my spreadsheet based on whether
they contain a formula or not.

is this possible?

 

[Bill Pfister]

http://www.j-walk.com/ss/excel/usertips/tip045.htm

Regards,
Bill

 

[ellebelle]

that is great, although what if i want the opposite effect. everytime i enter
a value manuallly i want to highlight it?

 

[Bill Pfister]

Set the cells so that they are normally highlighted and use the conditional
formatting to make the cell appear normal when the condition is true.

Instead of using the formula ("CellHasFormula") as a named-range, you can
use a user-defined function in the condition.

Put this code in any module of the workbook:

public function IsConstant()
IsConstant = not(application.caller.hasformula)
end function

Then use "=IsConstant()" as your formula in the conditional formatting.

 

[ellebelle]

that works, although can you get it to ignore empty cells?

 

[Bill Pfister]

Public Function IsConstant()
If (Len(Application.Caller.Value) = 0) Then
IsConstant = False
Else
IsConstant = Not (Application.Caller.HasFormula)
End If
End Function

 

[ellebelle]

thank you. that works perfectly.

Public Function IsConstant()
If (Len(Application.Caller.Value) = 0) Then
IsConstant = False
Else
IsConstant = Not (Application.Caller.HasFormula)
End If
End Function