Conditional Formatting for Changing a Formula

  • Thread starter Thread starter HeatherT
  • Start date Start date
H

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
 
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.
 
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
 
Back
Top