values v formulas with conditional formats
 

I wish to use conditional format type functionality to distinguish between
cells that have values only in versus cells with formulas in.

values v formulas with conditional formats
 

One way...

**Select cell A1** (this is important!!!)

Create this named formula
Goto the menu InsertNameDefine
Name: IsFormula
Refers to: =GET.CELL(48,A1)
OK

Now, apply the formatting

Select the cell(s) you want to format.
Goto the menu FormatConditional Formatting
Select the Formula Is option
Enter this formula in the box on the right:
=IsFormula
Click the Format button
Select the desired style(s)
OK out

Cells that contain formulas will have the format applied


--
Biff
Microsoft Excel MVP


"Riaan" wrote in message
...
I wish to use conditional format type functionality to distinguish between
cells that have values only in versus cells with formulas in.

values v formulas with conditional formats
 

Works 100%, thanks - are there other ways known to you?

"T. Valko" wrote:

One way...

**Select cell A1** (this is important!!!)

Create this named formula
Goto the menu InsertNameDefine
Name: IsFormula
Refers to: =GET.CELL(48,A1)
OK

Now, apply the formatting

Select the cell(s) you want to format.
Goto the menu FormatConditional Formatting
Select the Formula Is option
Enter this formula in the box on the right:
=IsFormula
Click the Format button
Select the desired style(s)
OK out

Cells that contain formulas will have the format applied


--
Biff
Microsoft Excel MVP


"Riaan" wrote in message
...
I wish to use conditional format type functionality to distinguish between
cells that have values only in versus cells with formulas in.

values v formulas with conditional formats
 

are there other ways known to you?

You could use a VBA user defined function. Since my "forte" is formulas if I
can do something through a formula I'll choose that method first.

Here's how to do it with a UDF:

Open the VBE editor: ALT F11
Open the Project Explorer: CTRL R
Locate your file name in the project explorer pane on the left. It'll look
something like this: VBAProject(your_file_name)
Right click the file name
Select: InsertModule
Copy the code below and paste it into the window on the right:

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

Return back to Excel: ALT Q

Then, you'd set the conditional formatting the same way but use this
formula:

=IsFormula(A1)

Replace A1 with the actual cell reference.

--
Biff
Microsoft Excel MVP


"Riaan" wrote in message
...
Works 100%, thanks - are there other ways known to you?

"T. Valko" wrote:

One way...

**Select cell A1** (this is important!!!)

Create this named formula
Goto the menu InsertNameDefine
Name: IsFormula
Refers to: =GET.CELL(48,A1)
OK

Now, apply the formatting

Select the cell(s) you want to format.
Goto the menu FormatConditional Formatting
Select the Formula Is option
Enter this formula in the box on the right:
=IsFormula
Click the Format button
Select the desired style(s)
OK out

Cells that contain formulas will have the format applied


--
Biff
Microsoft Excel MVP


"Riaan" wrote in message
...
I wish to use conditional format type functionality to distinguish
between
cells that have values only in versus cells with formulas in.

values v formulas with conditional formats
 

Brilliant, a simple answer to a major issues, thanks!

On Friday, April 24, 2009 7:54:19 AM UTC+1, T. Valko wrote:
are there other ways known to you?

You could use a VBA user defined function. Since my "forte" is formulas if I
can do something through a formula I'll choose that method first.

Here's how to do it with a UDF:

Open the VBE editor: ALT F11
Open the Project Explorer: CTRL R
Locate your file name in the project explorer pane on the left. It'll look
something like this: VBAProject(your_file_name)
Right click the file name
Select: InsertModule
Copy the code below and paste it into the window on the right:

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

Return back to Excel: ALT Q

Then, you'd set the conditional formatting the same way but use this
formula:

=IsFormula(A1)

Replace A1 with the actual cell reference.

--
Biff
Microsoft Excel MVP


"Riaan" wrote in message
...
Works 100%, thanks - are there other ways known to you?

"T. Valko" wrote:

One way...

**Select cell A1** (this is important!!!)

Create this named formula
Goto the menu InsertNameDefine
Name: IsFormula
Refers to: =GET.CELL(48,A1)
OK

Now, apply the formatting

Select the cell(s) you want to format.
Goto the menu FormatConditional Formatting
Select the Formula Is option
Enter this formula in the box on the right:
=IsFormula
Click the Format button
Select the desired style(s)
OK out

Cells that contain formulas will have the format applied


--
Biff
Microsoft Excel MVP


"Riaan" wrote in message
...
I wish to use conditional format type functionality to distinguish
between
cells that have values only in versus cells with formulas in.

values v formulas with conditional formats
 

I've been racking my brain for months on this.

works brilliantly - thanks

values v formulas with conditional formats
 

Thank You! Elegant solution!


On Thursday, April 23, 2009 11:26:17 AM UTC-5, T. Valko wrote:
One way...

**Select cell A1** (this is important!!!)

Create this named formula
Goto the menu InsertNameDefine
Name: IsFormula
Refers to: =GET.CELL(48,A1)
OK

Now, apply the formatting

Select the cell(s) you want to format.
Goto the menu FormatConditional Formatting
Select the Formula Is option
Enter this formula in the box on the right:
=IsFormula
Click the Format button
Select the desired style(s)
OK out

Cells that contain formulas will have the format applied


--
Biff
Microsoft Excel MVP


"Riaan" wrote in message
...
I wish to use conditional format type functionality to distinguish between
cells that have values only in versus cells with formulas in.

values v formulas with conditional formats
 

None of the two solutions works for me!? I have Excel 2013 - is that the problem?

values v formulas with conditional formats
 

On Monday, 20 May 2013 10:53:25 UTC+2, wrote:
None of the two solutions works for me!? I have Excel 2013 - is that the problem?

Try this ...

Step1 - Create a named formula
Select cell A1 (this is important!!!)
Formulas Name Manager New
Name: IsFormula
Refers to: =GET.CELL(48,A1)
OK

Step2 - Apply the formatting
Select the cell(s) you want to format.
Home Conditional Formatting Manage Rules New Rule
Select "Use a formula to determine which cells to format"
Enter this formula in the text box "Format values where this formula is true:
=IsFormula
Click the Format button
Select the desired style(s)
OK

values v formulas with conditional formats
 

On Friday, April 24, 2009 at 1:54:19 PM UTC+7, T. Valko wrote:
are there other ways known to you?

You could use a VBA user defined function. Since my "forte" is formulas if I
can do something through a formula I'll choose that method first.

Here's how to do it with a UDF:

Open the VBE editor: ALT F11
Open the Project Explorer: CTRL R
Locate your file name in the project explorer pane on the left. It'll look
something like this: VBAProject(your_file_name)
Right click the file name
Select: InsertModule
Copy the code below and paste it into the window on the right:

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

Return back to Excel: ALT Q

Then, you'd set the conditional formatting the same way but use this
formula:

=IsFormula(A1)

Replace A1 with the actual cell reference.

--
Biff
Microsoft Excel MVP


"Riaan" wrote in message
...
Works 100%, thanks - are there other ways known to you?

"T. Valko" wrote:

One way...

**Select cell A1** (this is important!!!)

Create this named formula
Goto the menu InsertNameDefine
Name: IsFormula
Refers to: =GET.CELL(48,A1)
OK

Now, apply the formatting

Select the cell(s) you want to format.
Goto the menu FormatConditional Formatting
Select the Formula Is option
Enter this formula in the box on the right:
=IsFormula
Click the Format button
Select the desired style(s)
OK out

Cells that contain formulas will have the format applied


--
Biff
Microsoft Excel MVP


"Riaan" wrote in message
...
I wish to use conditional format type functionality to distinguish
between
cells that have values only in versus cells with formulas in.


hi used this function below and it worked great UNTIL i pressed a macro to re sort rows, then it just came up with #VALUES, why is that? thx

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