Conditional format if cell = constant

[prakitpom]

My column B contains formula. But if someone changes it to a constant value, I would like to have it automatically highlighted in red. Can I use a conditional format function to do this? thanks...

 

[Marcelo Branco]

Jeff,

Tks!

Nice UDF too!

M.

 

[prakitpom]

Thanks a bunch everyone. It's really great.

 

[pechevarria]

Hi everybody, I like this method but I cant make it work in my excel. I use an Spanish 2013 version. Can you help me. Thank in advance for your work in this forum.

 

[pechevarria]

Hi,

Welcome to the forum!

Maybe this

Right-click in sheet-tab; pick View Code; paste the code below in the right-panel

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("B:B")) Is Nothing Then
        If Target.HasFormula Then
            Target.Interior.Color = xlNone
        Else
            Target.Interior.Color = vbRed
        End If
    End If
End Sub

HTH

M.

Hi everybody, I like this method but I cant make it work in my excel. I use an Spanish 2013 version. Can you help me. Thank in advance for your work in this forum.

 

[pechevarria]

Hello again. Finally I could make it work.

I only write this lines

        If Target.HasFormula Then
            Target.Font.Color = RGB(165, 0, 63)
        End If

Now every cell in that particular sheet where I make a formula appear with font in color. That is very useful for me.

But, it would be better if all my new workbook that I create look like this (formulas in color) without do anything anymore. Could be this possible??? Thanks

 

[Marcelo Branco]

But, it would be better if all my new workbook that I create look like this (formulas in color) without do anything anymore. Could be this possible??? Thanks

I do not know how to do this without a code, or even if it is possible.


M.

 

[pechevarria]

Thanks for your answer Marcelo.

I understand that it is needed a code but, is there any method to "tell" excel to used this code in all the sheets when I create a new workbook???

 

[Jonmo1]

I use an Spanish 2013 version

If you have XL2013, then you already have a built in function that tests for a formula.
In English, it's ISFORMULA, not sure the spanish equivelent.

So you can use
=NOT(ISFORMULA(A1))