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...

 

[West Man]

If the constant is text, then in conditional formatting - formula is try:=cell("type",$A$2)="l"

 

[Marcelo Branco]

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.

 

[prakitpom]

Thanks....I think this helps a lot.
But is there any easier way than coding the sheet?

The constant is a number, so the format has to be "value", not text.
thanks in advance.

 

[Marcelo Branco]

Thanks....I think this helps a lot.
But is there any easier way than coding the sheet?

The constant is a number, so the format has to be "value", not text.
thanks in advance.

I dont know how to do this without VBA.

The problem is that if you have, or not, a formula the format dont change and the CELL function doesnt help also (as far i as know...)

Maybe someone else can help you with a XML function.

M.

 

[repairman615]

Hello,

Welcome to the Message Board!

Marcelo Branco's provided VBA seems to be the easiest. It takes less than a minute (a few seconds if your familiar).

Not really sure this could be done without VBA.

However I do have a thought; seems I remember seeing a UDF HASFORMULA() somewhere. Maybe with that and hardcoding a formula to use with CF then maybe it could be done. Hardcoding as in giving a reference to every cell with a formula in the column.

 

[Marcelo Branco]

Hi,

I found a possible solution in Bill Jelen book (Excel Gurus Gone Wild) using the GET.CELL function ( i changed the formula a little bit)

Select A1
1. define a new name
Names Manager > New
Name: HasFormula
Refers to:=GET.CELL(48,A1)

original formula in BJ's book
=GET.CELL(INDIRECT("RC",False))
for some reason didnt work for me (???)

2. Select your range
Cond Formatting > New Rule > Use a formula to...
insert
=HasFormula=FALSE
pick a Format
ok, ok

HTH

M.

 

[Marcelo Branco]

-CORRECTION-

Original formula in BJ's book
=GET.CELL(48,INDIRECT("RC",FALSE))

M.

 

[Marcelo Branco]

Another correction

BJ's formula is perfect!

I forgot to translate to portuguese the string "RC", ie RowColumn, to "LC", LinhaColuna in portuguese

So using the translation

=INFO.CÉL(48;INDIRETO("LC";FALSO))

worked like a charm.

My apologies, BJ

M.

 

[repairman615]

Nice Marcelo!

I got it to work with:

=GET.CELL(48,INDIRECT("RC",FALSE))

No VBA Needed


Here is another way to do that, however It uses a User Defined Function (VBA).

Copy the following:

<font face=Courier New><SPAN style="color:#00007F">Function</SPAN> HASFORMULA(rng <SPAN style="color:#00007F">As</SPAN> Range)<br>    <SPAN style="color:#00007F">If</SPAN> rng.Count = 1 <SPAN style="color:#00007F">Then</SPAN><br>        HASFORMULA = rng.HASFORMULA<br>    <SPAN style="color:#00007F">Else</SPAN><br>        HASFORMULA = <SPAN style="color:#00007F">CVErr</SPAN>(xlErrRef)<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Function</SPAN></FONT>

In Excel Alt+F11
Keystrokes Alt - I - M to insert a module
paste

Then select the range and use conditional format 'use formula'

Use =Hasformula(A1)
select a format

The Reference A1 is the top/left cell in the range.

-Jeff