values v formulas with conditional formats

  • Thread starter Thread starter Riaan
  • Start date Start date
R

Riaan

I wish to use conditional format type functionality to distinguish between
cells that have values only in versus cells with formulas in.
 
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
 
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.
 
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
Click to expand...
 
None of the two solutions works for me!? I have Excel 2013 - is that the problem?
Click to expand...

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
 
Back
Top