Highlight cell where formula is overwritten

[samsnee]

Hi

I am trying to write a code where if someone keys over a formula in a cell so it is now value, the cell is highlighted. This includes if the cell also is linked to any additional files. Any ideas?

Thanks

 

[mart37]

Conditional Formatting: =isformula()

 

[FDibbins]

Conditional Formatting: =isformula()

1. That function is only available in later versions of excel, OP may not have a later version.
2. That will highlight all cells in the "applies to" range, that contain a formula. I think OP wants to ID cells that had formulas that were overwritten - in which case, they will need VBA

 

[samsnee]

How does isformula work? I tried putting it in as rule for conditional formatting but nothing happens.

I'm basically trying to highlight whenever someone hardkeys over a formula.

Thanks

 

[mart37]

Spreadsheet Formulas

Cell Formula C1 =5+5 D1 =NOT(ISFORMULA(C1)) <tbody> </tbody>

<tbody>
</tbody>

The formula from D1 can you place by a Conditional Format (new rule; apply to C1)

Excel tables to the web >> Excel Jeanie HTML 4

 

[MickG]

Try this:-

[COLOR="Navy"]Sub[/COLOR] MG23Aug22
Private [COLOR="Navy"]Sub[/COLOR] Worksheet_Change(ByVal Target [COLOR="Navy"]As[/COLOR] Range)
[COLOR="Navy"]Dim[/COLOR] oVal [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
[COLOR="Navy"]If[/COLOR] Target.Count = 1 [COLOR="Navy"]Then[/COLOR]
    oVal = Target.Formula
    [COLOR="Navy"]With[/COLOR] Application
        .EnableEvents = False
            .Undo
            [COLOR="Navy"]If[/COLOR] Target.HasFormula [COLOR="Navy"]Then[/COLOR] Target.Interior.Color = vbYellow
            Target = oVal
        .EnableEvents = True
    [COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[MickG]

The code above was incorrectly pasted, Try this:-

Private [COLOR="Navy"]Sub[/COLOR] Worksheet_Change(ByVal Target [COLOR="Navy"]As[/COLOR] Range)
[COLOR="Navy"]Dim[/COLOR] oVal [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
[COLOR="Navy"]If[/COLOR] Target.Count = 1 [COLOR="Navy"]Then[/COLOR]
    oVal = Target.Formula
    [COLOR="Navy"]With[/COLOR] Application
        .EnableEvents = False
            .Undo
            [COLOR="Navy"]If[/COLOR] Target.HasFormula [COLOR="Navy"]Then[/COLOR] Target.Interior.Color = vbYellow
            Target = oVal
        .EnableEvents = True
    [COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]If[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick