Automatically highlight values

[pagrender]

Hello,

Is there a way to make a worksheet change event that will evaluate the cells in the range G42:J60 and highlight any cell that does not contain a formula? Basically if someone "types over" one of my formulas with a constant value, I want it to stand out with a yellow-colored highlight.

My sheet name is "Start", if that is applicable.

The color I am using is listed in the code below (I took this from the macro recorder as I was trying to find the code for the color)

With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 10092543
.TintAndShade = 0
.PatternTintAndShade = 0
End With

Thanks for your help,
Pete

 

[MrKowz]

Give this a go:

Public Sub FindHardCode()
Dim rng As Range
Dim ws as worksheet
Set ws = Sheets("Start")
For Each rng In ws.Range("G42:J60")
   If ws.rng.Formula = ws.rng.Value Then
      ws.rng.Interior.ColorIndex = 6
   End If
Next rng
End Sub

 

[arkusM]

In a standard code module

Function IsFormula(Check_Cell As Range)
     IsFormula = Check_Cell.HasFormula
End Function

In conditional formatting
Use Formula Is from the drop daown and put in

=isformula(D4)

where D4 is one cell you want to check make sure it is not absoluted with $'s copy the formatting to the cells you want to check.

 

[pagrender]

for MrKowz: I'm getting a "Complile Error: Method or data member not found" and it's highlighting the first .rng of this part of your code:
If ws.rng.Formula = ws.rng.Value Then

 

[MrKowz]

Ahh - i may not have needed the ws part on that:

Public Sub FindHardCode()
Dim rng As Range
Dim ws as worksheet
Set ws = Sheets("Start")
For Each rng In ws.Range("G42:J60")
   If rng.Formula = rng.Value Then
      rng.Interior.ColorIndex = 6
   End If
Next rng
End Sub

 

[VoG]

Try

Private Sub Worksheet_Change(ByVal Target As Range)
With Range("G42:J60")
    .Interior.ColorIndex = xlNone
    On Error Resume Next
    .SpecialCells(xlCellTypeConstants).Interior.ColorIndex = 6
    On Error GoTo 0
End With
End Sub

 

[arkusM]

Hello,

Is there a way to make a worksheet change event that will evaluate the cells in the range G42:J60 and highlight any cell that does not contain a formula? Basically if someone "types over" one of my formulas with a constant value, I want it to stand out with a yellow-colored highlight.

My sheet name is "Start", if that is applicable.


Thanks for your help,
Pete

Uhm, there are now three ways of checking to see if someone overwrote the formula.

VoG's and MrKowz methods would be adaptable to prevent the formulas from being overwritten in the first place, which may be better than just highlighting the change.

Hmmmm should go back through my workbooks and see if this is feasible.... Thanks VoG and MrKowz.

 

[pagrender]

MrKowz: I still didn't get it to work with your code; however it worked with Vog's code. Thanks for your help.

Arkus: Thanks for the suggestion.

Vog: Thanks a bunch!

 

[arkusM]

In a standard code module

Function IsFormula(Check_Cell As Range)
     IsFormula = Check_Cell.HasFormula
End Function

In conditional formatting
Use Formula Is from the drop daown and put in

=[B][COLOR=red]NOT(isformula(D4[/COLOR][COLOR=red]))[/COLOR][/B]

where D4 is one cell you want to check make sure it is not absoluted with $'s copy the formatting to the cells you want to check.

oops!!