Identify external references (in formulas) and color format
Try this:
Create this UDF:
Function GetFormula(cell_ref As Range) As String
GetFormula = cell_ref.Formula
End Function
Then, the conditional format would use the Formula Is option.
Suppose cell A1 contains this formula:
=SUM('C:\TV\[test1.xls]Sheet1'!$A$1:$A$5)
Select cell A1
Goto FormatConditional Formatting
Formula Is:
=AND(LEFT(getformula(A1))="=",SEARCH(".xls",getfor mula(A1)))
Click the Format button
Select the style(s) desired
OK out
--
Biff
Microsoft Excel MVP
"Mike C" wrote in message
...
Hi,
I have a conditional format which allows me to identify/format all cells
that have formulas. In this instance it changes the font to Blue so that
every number that is blue, i know is a formula. I want to take it one step
further and maybe give the whole cell a color if the formula references an
external link, while all internal workbook links would just remain blue
font,
the external references in formulas would cause the whole cell to be
colored
(say orange). Any ideas?
--
Mike
|