Change color in cell if contains formula?

W

Wind54Surfer

Hi all,
I want to be able to have cell with formulas show with a different color.
I tried with conditional formatting but can't figure out how.

Can someone please help me.

Thanks in advance,
Emilio

Limited knowledge of Excel 2003
 
B

Bob Phillips

You need a UDF and test that in the CF.

Function IsFormula(rng As Range)
IsFormula = rng.HasFormula
End Function
 
D

Dave Peterson

You could use Format|Conditional formatting and a UserDefined Function.

In a General Module:

Option Explicit
Function IsFormula(rng As Range)
IsFormula = rng(1).HasFormula
End Function

Then back to excel and use this in your Format|conditional formatting rules:

Formula is: =isformula(A1)

(If A1 is the activecell.)

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm
 
E

Earl Kiosterud

Dave,

DANGER, WILL ROBINSON!!! :) Using a UDF in conditional formatting can
cause bizzare display problems. There's a knowledge base article about it.
Searching the knowledge base for udf conditional formatting didn't find it,
for some reason. I spent hours on a project that was acting as if Excel had
gone mad.
 
C

CLR

F5 > Special > Formulas > OK...........this will highlight all cells with
formulas therein.........

Then, while they are highlighted, right-click > FormatCells > PatternsTab >
choose a color > OK

Vaya con Dios,
Chuck, CABGx3
 
W

Wind54Surfer

Thanks for all the help,

Usted tambien
Emilio


CLR said:
F5 > Special > Formulas > OK...........this will highlight all cells with
formulas therein.........

Then, while they are highlighted, right-click > FormatCells > PatternsTab >
choose a color > OK

Vaya con Dios,
Chuck, CABGx3
Click to expand...
 
D

Dave Peterson

I've never seen that happen (but I do believe you).

Maybe the OP can use the UDF in a helper cell and then base that conditional
formatting on that helper cell?

(Do you recall if there's a problem with this suggestion?)
 
E

Earl Kiosterud

Dave,

I don't know, but I my guess is referring to a cell from CF containing a UDF
is OK. I'm pretty sure I ended up writing a Worksheet_Change routine with
the same logic as in the UDF. I haven't reloaded that client's stuff since
I replaced my disk drive last week and did a whole new install, so I don't
have quick access to that workbook. But I remember how I spent hours, with
strange things popping up on the screen (and wouldn't go away). I could
hear Twilight Zone music every time I worked on it. It was quite
intermittent, and would work OK for days at a time. I keep old versions of
workbook files in a project, so I started going back, and it quit failing at
the point where I'd put a UDF in CF. When I took that out of the most
current file, it worked fine. I posted in the MVP private group about it
after I found the solution.
 
D

Dave Peterson

Thanks for the background.



Earl said:
Dave,

I don't know, but I my guess is referring to a cell from CF containing a UDF
is OK. I'm pretty sure I ended up writing a Worksheet_Change routine with
the same logic as in the UDF. I haven't reloaded that client's stuff since
I replaced my disk drive last week and did a whole new install, so I don't
have quick access to that workbook. But I remember how I spent hours, with
strange things popping up on the screen (and wouldn't go away). I could
hear Twilight Zone music every time I worked on it. It was quite
intermittent, and would work OK for days at a time. I keep old versions of
workbook files in a project, so I started going back, and it quit failing at
the point where I'd put a UDF in CF. When I took that out of the most
current file, it worked fine. I posted in the MVP private group about it
after I found the solution.
Click to expand...
 
Top