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