Change color in cell if contains formula?

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

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

Wind54Surfer wrote:
>
> 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

--

Dave Peterson

You need a UDF and test that in the CF.

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


--
HTH

Bob Phillips

"Wind54Surfer" <Wind54Surfer@discussions.microsoft.com> wrote in message
news:511D1B59-704F-4BD7-9098-BB7CE25CAF53@microsoft.com...
> 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

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.
--
Earl Kiosterud
www.smokeylake.com

"Dave Peterson" <petersod@verizonXSPAM.net> wrote in message
news:42DA9C07.6D59F063@verizonXSPAM.net...
> 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
>
> Wind54Surfer wrote:
>>
>> 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
>
> --
>
> Dave Peterson

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


"Wind54Surfer" <Wind54Surfer@discussions.microsoft.com> wrote in message
news:511D1B59-704F-4BD7-9098-BB7CE25CAF53@microsoft.com...
> 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

Thanks for all the help,

Usted tambien
Emilio


"CLR" wrote:

> 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
>
>
> "Wind54Surfer" <Wind54Surfer@discussions.microsoft.com> wrote in message
> news:511D1B59-704F-4BD7-9098-BB7CE25CAF53@microsoft.com...
> > 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
>
>
>

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?)

Earl Kiosterud wrote:
>
> 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.
> --
> Earl Kiosterud
> www.smokeylake.com
>
> "Dave Peterson" <petersod@verizonXSPAM.net> wrote in message
> news:42DA9C07.6D59F063@verizonXSPAM.net...
> > 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
> >
> > Wind54Surfer wrote:
> >>
> >> 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
> >
> > --
> >
> > Dave Peterson

--

Dave Peterson

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.
--
Earl Kiosterud
www.smokeylake.com

"Dave Peterson" <petersod@verizonXSPAM.net> wrote in message
news:42DAD352.B934392B@verizonXSPAM.net...
> 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?)
>
> Earl Kiosterud wrote:
>>
>> 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.
>> --
>> Earl Kiosterud
>> www.smokeylake.com
>>
>> "Dave Peterson" <petersod@verizonXSPAM.net> wrote in message
>> news:42DA9C07.6D59F063@verizonXSPAM.net...
>> > 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
>> >
>> > Wind54Surfer wrote:
>> >>
>> >> 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
>> >
>> > --
>> >
>> > Dave Peterson
>
> --
>
> Dave Peterson

Thanks for the background.



Earl Kiosterud wrote:
>
> 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.
> --
> Earl Kiosterud
> www.smokeylake.com
>
> "Dave Peterson" <petersod@verizonXSPAM.net> wrote in message
> news:42DAD352.B934392B@verizonXSPAM.net...
> > 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?)
> >
> > Earl Kiosterud wrote:
> >>
> >> 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.
> >> --
> >> Earl Kiosterud
> >> www.smokeylake.com
> >>
> >> "Dave Peterson" <petersod@verizonXSPAM.net> wrote in message
> >> news:42DA9C07.6D59F063@verizonXSPAM.net...
> >> > 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
> >> >
> >> > Wind54Surfer wrote:
> >> >>
> >> >> 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
> >> >
> >> > --
> >> >
> >> > Dave Peterson
> >
> > --
> >
> > Dave Peterson

--

Dave Peterson