Conditional Format if not formula

  • Thread starter Thread starter Gary
  • Start date Start date
G

Gary

I wish to use Conditional Formatting to highlight a cell if a formula is
overwritten by a manual number. The cells typically have formulas but there
are times a manual number must be inserted in the cell, it is then I want
the cell to be highlighted. Is there a formula that can be used in
Conditional Formatting that will do this? Thanks for your time, Gary
 
Gary,

You could use the change event: copy the code below, right-click the sheet tab, select "View Code"
and paste the code into the window that appears.

You can either remove or modify this line

If Intersect(Target, Range("A2:D100")) Is Nothing Then Exit Sub

That limits the coloring to A2:D100 - change that to the range with formulas that might be
overwritten, or remove it to highlight any non-formula entry.

If it applies the format to a cell that you don't actually want highlighted, then just remove it.

HTH,
Bernie
MS Excel MVP


Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Target.HasFormula Then Exit Sub
If Intersect(Target, Range("A2:D100")) Is Nothing Then Exit Sub
Target.Interior.ColorIndex = 3
End Sub
 
One more method for Gary.

Copy this UDF to a general module in your workbook.

Function IsFormula(Cell)
IsFormula = Cell.HasFormula
End Function

Select the cells and Format>CF>Formula is

=NOT(IsFormula(cellref))


Gord Dibben MS Excel MVP
 
Thanks all, works perfect...


Gord Dibben said:
One more method for Gary.

Copy this UDF to a general module in your workbook.

Function IsFormula(Cell)
IsFormula = Cell.HasFormula
End Function

Select the cells and Format>CF>Formula is

=NOT(IsFormula(cellref))


Gord Dibben MS Excel MVP
Click to expand...
 
Back
Top