Conditional format if cell contains formula

  • Thread starter Thread starter Jean-Marc
  • Start date Start date
J

Jean-Marc

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?
 
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
 
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.
 
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
 
Excellent - works great. Thanks. You are a guru.

Peo Sjoblom said:
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
Click to expand...
 
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
 
Thanks. Very elegant solution.

T. Valko said:
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
Click to expand...
 
Hi, I have done this the UDF way, but now excel behaves strangely. I have applied the conditional formatting to cells in Sheet2. If I want to pick up a value from sheet 2 in sheet1 (formula in sheet 1 A1: ='Sheet2'!B6), nothing happend in sheet 1. Instead excel inputs a formula in a random cell in sheet 2 which refers to itself creating a circular reference. When I clear the conditional formatting from sheet 2 all is fine. Has anyone experienced this?
 
Hi, I have done this the UDF way, but now excel behaves strangely. I have applied the conditional formatting to cells in Sheet2. If I want to pick up a value from sheet 2 in sheet1 (formula in sheet 1 A1: ='Sheet2'!B6), nothing happend in sheet 1. Instead excel inputs a formula in a random cell in sheet 2 which refers to itself creating a circular reference. When I clear the conditional formatting from sheet 2 all is fine. Has anyone experienced this? I am working in Excel 2007.

thanks
 
Post the UDF code and describe how you are using it in connection with CF.


Gord Dibben MS Excel MVP
 
Hi,

The UDF code is:

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

The conditional formatting formula I use is:
=isformula(A1)
I then select the cells I want to apply this to in the applies to section.

I have another conditional format to format all other text differently:
=ISBLANK(A1) = FALSE

So in sheet 1 I enter in E31: = 'Sheet2'!C28. when I hit enter E31 in Sheet
1 remains blank but in sheet2'!E32 a formula is entered saying Sheet1!D26.
 
I see what you mean but only if I enter an = sign in sheet1 E31 then point
and click on sheet2 C28.

If I enter =sheet2!C28 all works OK.

I cannot explain why the point and click reacts as it does.


Gord
 
I have the same problem with this function - was a solution ever found?



Gord Dibben wrote:

I see what you mean but only if I enter an = sign in sheet1 E31 then pointand
16-Oct-08

I see what you mean but only if I enter an = sign in sheet1 E31 then poin
and click on sheet2 C28

If I enter =sheet2!C28 all works OK

I cannot explain why the point and click reacts as it does

Gor

On Thu, 16 Oct 2008 03:09:00 -0700, Bertu

Previous Posts In This Thread:

Conditional format if cell contains formula
Is there a way (conditional formatting?) to apply a different format to cell
that contain a formula, as opposed to cells that have a directly entere
value?

sure.
sure

here's an exampl

A B C D E F
1 no1 no2 no3 no4 no5 no
2 ticket1 2 15 26 27 36 3
3 ticket2 1 12 13 15 24 3
4 ticket3 3 5 20 28 37 4

6 drawn 3 15 25 26 47 4

1. Select cells B2:G
2. Select Conditional Formattin
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 O

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 Hen


:

Thanks, but you misunderstood my question.
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

:

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

=GET.CELL(6,Sheet1!$A1

and in the name box type in something descriptive lik

FormulaI

click O

Select A1:A
with A1 as the active cell, do format>conditional formatting, select formula
i
and us

=LEFT(FormulaIs)="=

or in a pedagogic manne

=LEFT(FormulaIs,1)="=

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

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

--

Regards

Peo Sjoblo




Re: Conditional format if cell contains formula
Excellent - works great. Thanks. You are a guru

:

Re: Conditional format if cell contains formula
You can use a UDF (user defined function)

Function IsFormula(cell_ref As Range
IsFormula = cell_ref.HasFormul
End Functio

To use this

Open the VBE editor: ALT F1
Open the Project Explorer: CTRL
Locate your file name in the project explorer pane on the left
Right click the file nam
Select: Insert>Modul
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



Re: Conditional format if cell contains formula
Thanks. Very elegant solution.

:

You're welcome. Thanks for the feedback!
You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP

problem with udf
Hi, I have done this the UDF way, but now excel behaves strangely. I have applied the conditional formatting to cells in Sheet2. If I want to pick up a value from sheet 2 in sheet1 (formula in sheet 1 A1: ='Sheet2'!B6), nothing happend in sheet 1. Instead excel inputs a formula in a random cell in sheet 2 which refers to itself creating a circular reference. When I clear the conditional formatting from sheet 2 all is fine. Has anyone experienced this?

Conditional format if cell contains formula
Hi, I have done this the UDF way, but now excel behaves strangely. I have applied the conditional formatting to cells in Sheet2. If I want to pick up a value from sheet 2 in sheet1 (formula in sheet 1 A1: ='Sheet2'!B6), nothing happend in sheet 1. Instead excel inputs a formula in a random cell in sheet 2 which refers to itself creating a circular reference. When I clear the conditional formatting from sheet 2 all is fine. Has anyone experienced this? I am working in Excel 2007.

thanks

Re: problem with udf
Hi,

The UDF code is:

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

The conditional formatting formula I use is:
=isformula(A1)
I then select the cells I want to apply this to in the applies to section.

I have another conditional format to format all other text differently:
=ISBLANK(A1) = FALSE

So in sheet 1 I enter in E31: = 'Sheet2'!C28. when I hit enter E31 in Sheet
1 remains blank but in sheet2'!E32 a formula is entered saying Sheet1!D26.








:

I see what you mean but only if I enter an = sign in sheet1 E31 then pointand
I see what you mean but only if I enter an = sign in sheet1 E31 then point
and click on sheet2 C28.

If I enter =sheet2!C28 all works OK.

I cannot explain why the point and click reacts as it does.


Gord

On Thu, 16 Oct 2008 03:09:00 -0700, Bertus

how to use this UDF to format that a formual is not present :-)
You are required to be a member to post replies. After logging in or becoming a member, you will be redirected back to this page.


Submitted via EggHeadCafe - Software Developer Portal of Choice
Adding WCF Service References
http://www.eggheadcafe.com/tutorial...9-dfa51a9fab8e/adding-wcf-service-refere.aspx
 
How can we apply this in openoffice calc spreadsheets??
what modifications will be require?
 
Hi Biff,

I used your suggestion and it worked perfectly! It was exactly what I wanted to do...
But after I saved my file the conditional formatting function didn't work anymore... I have no idea why?!?

So when I look in the creatded module thingy it still states
your suggested formula:

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

Also the conditional formatting formula is still there =IsFormula(M1) (M in my case)
It is supposed to highlight cells with formula in red colour and values (no formulas) in white (actually in green since this is my background colour).
Anyhow... everything is green after I saved the file.
Any suggestions from your side?

Thanks in advance and best regards,
Martin
 
Back
Top