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

--
Biff
Microsoft Excel MVP

"Riaan" <Ri...@discussions.microsoft.com> wrote in message
news:7DE8D979-0707-48CC...@microsoft.com...

[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.

--
Biff
Microsoft Excel MVP

"Riaan" <Ri...@discussions.microsoft.com> wrote in message

news:5A9B153C-079B-42BC...@microsoft.com...

[r...@tonks.me]

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

[Stig]

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

works brilliantly - thanks

[barnaba...@gmail.com]

Thank You! Elegant solution!

[and...@rosenbeck.com]

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

[Moreshwar]

On Monday, 20 May 2013 10:53:25 UTC+2, and...@rosenbeck.com 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:

[jingkie...@gmail.com]

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