(Msg. 1) Posted: Wed Aug 13, 2008 9:59 pm
Post subject: Conditional format if cell contains formula Archived from groups: microsoft>public>excel>worksheet>functions (more info?)

Is there a way (conditional formatting?) to apply a different format to cells
that contain a formula, as opposed to cells that have a directly entered
value?

(Msg. 2) Posted: Thu Aug 14, 2008 1:09 am
Post subject: RE: Conditional format if cell contains formula [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)

sure.

here's an example

A B C D E F G
1 no1 no2 no3 no4 no5 no6
2 ticket1 2 15 26 27 36 38
3 ticket2 1 12 13 15 24 34
4 ticket3 3 5 20 28 37 40
5
6 drawn 3 15 25 26 47 49

1. Select cells B2:G4
2. Select Conditional Formatting
3. Choose Formula Is (as opposed to Cell value is)
4. For the formula, use the CountIf function:
=COUNTIF($B$6:$G$6,B2)
5. Click the Format button.
6. Select formatting options (choose a color for the cell), click OK

What happens is that the conditional formatting formula will evaluate each
cell to see if it should "count" according to the criteria. Once it is
"counted", it will apply the format to the cell.

Try it, its fun.

Yong Heng

"Jean-Marc" wrote:

> Is there a way (conditional formatting?) to apply a different format to cells
> that contain a formula, as opposed to cells that have a directly entered
> value?

(Msg. 3) Posted: Thu Aug 14, 2008 1:39 am
Post subject: RE: Conditional format if cell contains formula [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)

Thanks, but you misunderstood my question. I am familiar with the operation
of conditional formats. Here is a simple example of what I want to achieve:
Cell A1 contains value '1'
Cell A2 contains value '2'
Cell A3 contains formula '=SUM(A1:A2)'

Now what I would like is for cell A3 to be shown on a blue background,
because it is a formula, whilst cells A1 and A2 should remain in the default
format, because they are manually entered values. Basically is there an Excel
function that is able to detect whether a cell is a formula or a single
value? In other words, if the cell entry starts with '=', then I would like
it to be formatted differently. Sounds easy, but I am stumped. Excel seems to
only consider the *Value* in a cell, but not whether that value comes from a
manual entry or a formula calculation.

"Yong Heng" wrote:

> sure.
>
> here's an example
>
> A B C D E F G
> 1 no1 no2 no3 no4 no5 no6
> 2 ticket1 2 15 26 27 36 38
> 3 ticket2 1 12 13 15 24 34
> 4 ticket3 3 5 20 28 37 40
> 5
> 6 drawn 3 15 25 26 47 49
>
> 1. Select cells B2:G4
> 2. Select Conditional Formatting
> 3. Choose Formula Is (as opposed to Cell value is)
> 4. For the formula, use the CountIf function:
> =COUNTIF($B$6:$G$6,B2)
> 5. Click the Format button.
> 6. Select formatting options (choose a color for the cell), click OK
>
> What happens is that the conditional formatting formula will evaluate each
> cell to see if it should "count" according to the criteria. Once it is
> "counted", it will apply the format to the cell.
>
> Try it, its fun.
>
> Yong Heng
>
>
> "Jean-Marc" wrote:
>
> > Is there a way (conditional formatting?) to apply a different format to cells
> > that contain a formula, as opposed to cells that have a directly entered
> > value?

(Msg. 4) Posted: Thu Aug 14, 2008 2:17 am
Post subject: Re: Conditional format if cell contains formula [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)

You can use Excel 4 macro in a defined name formula to check if cell has a
formula, using you example
do insert>name>define, in the source box put

=GET.CELL(6,Sheet1!$A1)

and in the name box type in something descriptive like

FormulaIs

click OK

Select A1:A3
with A1 as the active cell, do format>conditional formatting, select formula
is
and use

=LEFT(FormulaIs)="="

or in a pedagogic manner

=LEFT(FormulaIs,1)="="

you can actually leave out 1 if you just want one character

now click the format button and select format and click OK twice

--

Regards,

Peo Sjoblom

"Jean-Marc" wrote in message

> Thanks, but you misunderstood my question. I am familiar with the
> operation
> of conditional formats. Here is a simple example of what I want to
> achieve:
> Cell A1 contains value '1'
> Cell A2 contains value '2'
> Cell A3 contains formula '=SUM(A1:A2)'
>
> Now what I would like is for cell A3 to be shown on a blue background,
> because it is a formula, whilst cells A1 and A2 should remain in the
> default
> format, because they are manually entered values. Basically is there an
> Excel
> function that is able to detect whether a cell is a formula or a single
> value? In other words, if the cell entry starts with '=', then I would
> like
> it to be formatted differently. Sounds easy, but I am stumped. Excel seems
> to
> only consider the *Value* in a cell, but not whether that value comes from
> a
> manual entry or a formula calculation.
>
> "Yong Heng" wrote:
>
>> sure.
>>
>> here's an example
>>
>> A B C D E F G
>> 1 no1 no2 no3 no4 no5 no6
>> 2 ticket1 2 15 26 27 36 38
>> 3 ticket2 1 12 13 15 24 34
>> 4 ticket3 3 5 20 28 37 40
>> 5
>> 6 drawn 3 15 25 26 47 49
>>
>> 1. Select cells B2:G4
>> 2. Select Conditional Formatting
>> 3. Choose Formula Is (as opposed to Cell value is)
>> 4. For the formula, use the CountIf function:
>> =COUNTIF($B$6:$G$6,B2)
>> 5. Click the Format button.
>> 6. Select formatting options (choose a color for the cell), click OK
>>
>> What happens is that the conditional formatting formula will evaluate
>> each
>> cell to see if it should "count" according to the criteria. Once it is
>> "counted", it will apply the format to the cell.
>>
>> Try it, its fun.
>>
>> Yong Heng
>>
>>
>> "Jean-Marc" wrote:
>>
>> > Is there a way (conditional formatting?) to apply a different format to
>> > cells
>> > that contain a formula, as opposed to cells that have a directly
>> > entered
>> > value?

(Msg. 5) Posted: Thu Aug 14, 2008 2:37 am
Post subject: Re: Conditional format if cell contains formula [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)

Excellent - works great. Thanks. You are a guru.

"Peo Sjoblom" wrote:

> You can use Excel 4 macro in a defined name formula to check if cell has a
> formula, using you example
> do insert>name>define, in the source box put
>
> =GET.CELL(6,Sheet1!$A1)
>
> and in the name box type in something descriptive like
>
> FormulaIs
>
> click OK
>
>
> Select A1:A3
> with A1 as the active cell, do format>conditional formatting, select formula
> is
> and use
>
> =LEFT(FormulaIs)="="
>
> or in a pedagogic manner
>
> =LEFT(FormulaIs,1)="="
>
> you can actually leave out 1 if you just want one character
>
> now click the format button and select format and click OK twice
>
>
> --
>
>
> Regards,
>
>
> Peo Sjoblom
>
>
>
>
>
> "Jean-Marc" wrote in message
> > > Thanks, but you misunderstood my question. I am familiar with the
> > operation
> > of conditional formats. Here is a simple example of what I want to
> > achieve:
> > Cell A1 contains value '1'
> > Cell A2 contains value '2'
> > Cell A3 contains formula '=SUM(A1:A2)'
> >
> > Now what I would like is for cell A3 to be shown on a blue background,
> > because it is a formula, whilst cells A1 and A2 should remain in the
> > default
> > format, because they are manually entered values. Basically is there an
> > Excel
> > function that is able to detect whether a cell is a formula or a single
> > value? In other words, if the cell entry starts with '=', then I would
> > like
> > it to be formatted differently. Sounds easy, but I am stumped. Excel seems
> > to
> > only consider the *Value* in a cell, but not whether that value comes from
> > a
> > manual entry or a formula calculation.
> >
> > "Yong Heng" wrote:
> >
> >> sure.
> >>
> >> here's an example
> >>
> >> A B C D E F G
> >> 1 no1 no2 no3 no4 no5 no6
> >> 2 ticket1 2 15 26 27 36 38
> >> 3 ticket2 1 12 13 15 24 34
> >> 4 ticket3 3 5 20 28 37 40
> >> 5
> >> 6 drawn 3 15 25 26 47 49
> >>
> >> 1. Select cells B2:G4
> >> 2. Select Conditional Formatting
> >> 3. Choose Formula Is (as opposed to Cell value is)
> >> 4. For the formula, use the CountIf function:
> >> =COUNTIF($B$6:$G$6,B2)
> >> 5. Click the Format button.
> >> 6. Select formatting options (choose a color for the cell), click OK
> >>
> >> What happens is that the conditional formatting formula will evaluate
> >> each
> >> cell to see if it should "count" according to the criteria. Once it is
> >> "counted", it will apply the format to the cell.
> >>
> >> Try it, its fun.
> >>
> >> Yong Heng
> >>
> >>
> >> "Jean-Marc" wrote:
> >>
> >> > Is there a way (conditional formatting?) to apply a different format to
> >> > cells
> >> > that contain a formula, as opposed to cells that have a directly
> >> > entered
> >> > value?
>
>
>

(Msg. 6) Posted: Thu Aug 14, 2008 5:59 am
Post subject: Re: Conditional format if cell contains formula [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)

You can use a UDF (user defined function):

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

To use this:

Open the VBE editor: ALT F11
Open the Project Explorer: CTRL R
Locate your file name in the project explorer pane on the left.
Right click the file name
Select: Insert>Module
Copy the code above and paste into the window on the right
Return back to Excel: ALT Q

Set the conditional formatting...
Select the cell(s) in question. Assume this is cell A1.
Goto Format>Conditional Formatting
Formula Is: =IsFormula(A1)
Click the Format button
Select the desired style(s)
OK out

--
Biff
Microsoft Excel MVP

"Jean-Marc" wrote in message

> Is there a way (conditional formatting?) to apply a different format to
> cells
> that contain a formula, as opposed to cells that have a directly entered
> value?

(Msg. 7) Posted: Thu Aug 14, 2008 5:59 am
Post subject: Re: Conditional format if cell contains formula [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)

Thanks. Very elegant solution.

"T. Valko" wrote:

> You can use a UDF (user defined function):
>
> Function IsFormula(cell_ref As Range)
> IsFormula = cell_ref.HasFormula
> End Function
>
> To use this:
>
> Open the VBE editor: ALT F11
> Open the Project Explorer: CTRL R
> Locate your file name in the project explorer pane on the left.
> Right click the file name
> Select: Insert>Module
> Copy the code above and paste into the window on the right
> Return back to Excel: ALT Q
>
> Set the conditional formatting...
> Select the cell(s) in question. Assume this is cell A1.
> Goto Format>Conditional Formatting
> Formula Is: =IsFormula(A1)
> Click the Format button
> Select the desired style(s)
> OK out
>
>
> --
> Biff
> Microsoft Excel MVP
>
>
> "Jean-Marc" wrote in message
> > > Is there a way (conditional formatting?) to apply a different format to
> > cells
> > that contain a formula, as opposed to cells that have a directly entered
> > value?
>
>
>

(Msg. 8) Posted: Thu Aug 14, 2008 5:32 pm
Post subject: Re: Conditional format if cell contains formula [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP

"Jean-Marc" wrote in message

> Thanks. Very elegant solution.
>
> "T. Valko" wrote:
>
>> You can use a UDF (user defined function):
>>
>> Function IsFormula(cell_ref As Range)
>> IsFormula = cell_ref.HasFormula
>> End Function
>>
>> To use this:
>>
>> Open the VBE editor: ALT F11
>> Open the Project Explorer: CTRL R
>> Locate your file name in the project explorer pane on the left.
>> Right click the file name
>> Select: Insert>Module
>> Copy the code above and paste into the window on the right
>> Return back to Excel: ALT Q
>>
>> Set the conditional formatting...
>> Select the cell(s) in question. Assume this is cell A1.
>> Goto Format>Conditional Formatting
>> Formula Is: =IsFormula(A1)
>> Click the Format button
>> Select the desired style(s)
>> OK out
>>
>>
>> --
>> Biff
>> Microsoft Excel MVP
>>
>>
>> "Jean-Marc" wrote in message
>> >> > Is there a way (conditional formatting?) to apply a different format to
>> > cells
>> > that contain a formula, as opposed to cells that have a directly
>> > entered
>> > value?
>>
>>
>>

All times are: Eastern Time (US & Canada) Goto page 1, 2, 3

Page 1 of 3

You can post new topics in this forum You can reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum