values v formulas with conditional formats

[Riaan]

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

 

[T. Valko]

One way...

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

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

Now, apply the formatting

Select the cell(s) you want to format.
Goto the menu Format>Conditional 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

 

[Riaan]

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

 

[T. Valko]

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: Insert>Module
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.

 

[rob]

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

 

[Stig]

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

works brilliantly - thanks

 

[barnabas.helmy]

Thank You! Elegant solution!

One way...

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

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

Now, apply the formatting

Select the cell(s) you want to format.
Goto the menu Format>Conditional 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

 

[anders]

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

 

[Moreshwar]

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