Conditional Format if not formula

[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

 

[Bernie Deitrick]

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("A2100")) Is Nothing Then Exit Sub

That limits the coloring to A2100 - 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("A2100")) Is Nothing Then Exit Sub
Target.Interior.ColorIndex = 3
End Sub

 

[Gord Dibben]

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

 

[Gary]

Thanks all, works perfect...

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