Conditional Formatting for Changing a Formula

[HeatherT]

Hi,
I'd like a cell to change color when a user has entered a hard-coded number
rather than the keeping the formula already in the cell.
Any ideas?

Thanks,
Heather

 

[T. Valko]

See this:

http://groups.google.com/group/micr...c/browse_thread/thread/6bf323d2cf859ecd?tvc=2

 

[Shane Devenshire]

Hi,

You can use a VBA macro:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim isect As Range
Set isect = Application.Intersect(Target, Range("A1"))
If Not isect Is Nothing Then
If IsNumeric(Target) And Not Target.HasFormula And Target <> "" Then
Target.Interior.ColorIndex = 6
End If
End If
End Sub

Since you are asking for a method to change the cell color when there is a
hard coded NUMBER entered in the cell, not when the cell is cleared or when a
text entry is made, then the above macro will do what you want.

 

[HeatherT]

Thanks to all ... all seemed similar to answer I found somewhere else and
ended up using.

Created a general module in VBE for the workbook:
Function IsFormula(Cell)
IsFormula = Cell.HasFormula
End Function

Then used conditional formatting:
CF->Formula Is->=NOT(IsFormula(CellRef))

Hope that helps for others as well!

Heather