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