Identify external references (in formulas) and color format

[Mike C]

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?

 

[T. Valko]

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 Format>Conditional Formatting
Formula Is:

=AND(LEFT(getformula(A1))="=",SEARCH(".xls",getformula(A1)))

Click the Format button
Select the style(s) desired
OK out

 

[Mike C]

Biff,

Thanks, i think this will work and i have a couple of questions..

UDF? is this User Defined Function? If so, where to i go to set this up?
Thanks. Sorry to ask what is probably a simple question..if this is a macro
how would i set it up (as i am not familiar with Macros). If it is something
else can you let me know?

I appreciate you answering my post and i think this will work, just need a
little help getting it set up.
--
Mike

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 Format>Conditional Formatting
Formula Is:

=AND(LEFT(getformula(A1))="=",SEARCH(".xls",getformula(A1)))

Click the Format button
Select the style(s) desired
OK out

--
Biff
Microsoft Excel MVP

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?

 

[T. Valko]

UDF? is this User Defined Function?

Yes

If so, where to i go to set this up?

Let's make a slight change:

Function GetFormula(cell_ref As Range) As String
Application.Volatile
GetFormula = cell_ref.Formula
End Function

Open the VBE editor: ALT F11
Open the Project Explorer: CTRL R
Locate your file name in the pane that opens
Select the file name then right click
Select Insert>Module

Copy/paste the UDF code into the window that opens
Return to Excel: ALT Q

Then apply the cf.


--
Biff
Microsoft Excel MVP

Biff,

Thanks, i think this will work and i have a couple of questions..

UDF? is this User Defined Function? If so, where to i go to set this up?
Thanks. Sorry to ask what is probably a simple question..if this is a
macro
how would i set it up (as i am not familiar with Macros). If it is
something
else can you let me know?

I appreciate you answering my post and i think this will work, just need a
little help getting it set up.
--
Mike

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 Format>Conditional Formatting
Formula Is:

=AND(LEFT(getformula(A1))="=",SEARCH(".xls",getformula(A1)))

Click the Format button
Select the style(s) desired
OK out

--
Biff
Microsoft Excel MVP

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

Bif,

Thanks for your help..I got it to work on a test (one sheet workbook), but
can't seem to get it to run for all pages in the workbook. Not sure why as I
copied and pasted the info that you provided.

Essentially, what i want to do is to format cells in 3 ways:
If formula, change font to BLUE
If formula is external reference, color cell Orange
If cell has a number that is hard coded, color cell Green
If cell is blank, leave alone.

Is there a way to combine all of this into the module or cf piece.

thanks again,

-mike


--
Mike

UDF? is this User Defined Function?
Yes

If so, where to i go to set this up?

Let's make a slight change:

Function GetFormula(cell_ref As Range) As String
Application.Volatile
GetFormula = cell_ref.Formula
End Function

Open the VBE editor: ALT F11
Open the Project Explorer: CTRL R
Locate your file name in the pane that opens
Select the file name then right click
Select Insert>Module

Copy/paste the UDF code into the window that opens
Return to Excel: ALT Q

Then apply the cf.


--
Biff
Microsoft Excel MVP

Biff,

Thanks, i think this will work and i have a couple of questions..

UDF? is this User Defined Function? If so, where to i go to set this up?
Thanks. Sorry to ask what is probably a simple question..if this is a
macro
how would i set it up (as i am not familiar with Macros). If it is
something
else can you let me know?

I appreciate you answering my post and i think this will work, just need a
little help getting it set up.
--
Mike

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 Format>Conditional Formatting
Formula Is:

=AND(LEFT(getformula(A1))="=",SEARCH(".xls",getformula(A1)))

Click the Format button
Select the style(s) desired
OK out

--
Biff
Microsoft Excel MVP


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

I think i see why this didn't work for the whole workbook and cells..

is it in the conditional formatting, where i reference cell (A1)?
=AND(LEFT(getformula(A1))="=",SEARCH(".xls",getformula(A1)))

if this is the case what to i put in there to make sure all cells are
checked..I clicked on the upper left corner of the worksheet so the whole
worksheet was selected, then applied this cond format..nothing changed...Also
i have this listed as the second condition..I wonder if that is the issue as
well.
--
Mike

UDF? is this User Defined Function?
Yes

If so, where to i go to set this up?

Let's make a slight change:

Function GetFormula(cell_ref As Range) As String
Application.Volatile
GetFormula = cell_ref.Formula
End Function

Open the VBE editor: ALT F11
Open the Project Explorer: CTRL R
Locate your file name in the pane that opens
Select the file name then right click
Select Insert>Module

Copy/paste the UDF code into the window that opens
Return to Excel: ALT Q

Then apply the cf.


--
Biff
Microsoft Excel MVP

Biff,

Thanks, i think this will work and i have a couple of questions..

UDF? is this User Defined Function? If so, where to i go to set this up?
Thanks. Sorry to ask what is probably a simple question..if this is a
macro
how would i set it up (as i am not familiar with Macros). If it is
something
else can you let me know?

I appreciate you answering my post and i think this will work, just need a
little help getting it set up.
--
Mike

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 Format>Conditional Formatting
Formula Is:

=AND(LEFT(getformula(A1))="=",SEARCH(".xls",getformula(A1)))

Click the Format button
Select the style(s) desired
OK out

--
Biff
Microsoft Excel MVP


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?

 

[T. Valko]

It works for me regardless of which sheet the formatting is applied to.

Here are the conditional formatting conditions:

Condition 1
If formula is external reference, color cell Orange
Formula Is:
=AND(LEFT(getformula(A1))="=",SEARCH(".xls",getformula(A1)))

Condition 2
If formula, change font to BLUE
Formula Is:
=LEFT(getformula(A1))="="

Condition 3
If cell has a number that is hard coded, color cell Green
Formula Is:
=AND(LEFT(getformula(A1))<>"=",ISNUMBER(A1))

NB: condition 2 is kind of ambiguous. If you have a TEXT string in a cell
that begins with an equal sign this will be evaluated as a FORMULA. The
problem is in identifying formulas. Every formula starts with an equal sign
but after that it's hard to find another character that is exclusive to a
formula. For example, =A1 is a formula and '=A1 is a text string. Both start
with an equal sign. You might think that you could look for ( ) which *most*
formulas contain but *not all* formulas have ( ) as demonstrated with =A1.

If you might have TEXT strings that start with an equal sign I don't how to
distinguish those from actual formulas. I'm sure it could be done with a
more elaborate UDF but I'm not the best programmer.

--
Biff
Microsoft Excel MVP

Bif,

Thanks for your help..I got it to work on a test (one sheet workbook), but
can't seem to get it to run for all pages in the workbook. Not sure why as
I
copied and pasted the info that you provided.

Essentially, what i want to do is to format cells in 3 ways:
If formula, change font to BLUE
If formula is external reference, color cell Orange
If cell has a number that is hard coded, color cell Green
If cell is blank, leave alone.

Is there a way to combine all of this into the module or cf piece.

thanks again,

-mike


--
Mike

UDF? is this User Defined Function?
Yes

If so, where to i go to set this up?

Let's make a slight change:

Function GetFormula(cell_ref As Range) As String
Application.Volatile
GetFormula = cell_ref.Formula
End Function

Open the VBE editor: ALT F11
Open the Project Explorer: CTRL R
Locate your file name in the pane that opens
Select the file name then right click
Select Insert>Module

Copy/paste the UDF code into the window that opens
Return to Excel: ALT Q

Then apply the cf.


--
Biff
Microsoft Excel MVP

Biff,

Thanks, i think this will work and i have a couple of questions..

UDF? is this User Defined Function? If so, where to i go to set this
up?
Thanks. Sorry to ask what is probably a simple question..if this is a
macro
how would i set it up (as i am not familiar with Macros). If it is
something
else can you let me know?

I appreciate you answering my post and i think this will work, just
need a
little help getting it set up.
--
Mike


:

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 Format>Conditional Formatting
Formula Is:

=AND(LEFT(getformula(A1))="=",SEARCH(".xls",getformula(A1)))

Click the Format button
Select the style(s) desired
OK out

--
Biff
Microsoft Excel MVP


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

Thank you..this worked.
--
Mike

It works for me regardless of which sheet the formatting is applied to.

Here are the conditional formatting conditions:

Condition 1
If formula is external reference, color cell Orange
Formula Is:
=AND(LEFT(getformula(A1))="=",SEARCH(".xls",getformula(A1)))

Condition 2
If formula, change font to BLUE
Formula Is:
=LEFT(getformula(A1))="="

Condition 3
If cell has a number that is hard coded, color cell Green
Formula Is:
=AND(LEFT(getformula(A1))<>"=",ISNUMBER(A1))

NB: condition 2 is kind of ambiguous. If you have a TEXT string in a cell
that begins with an equal sign this will be evaluated as a FORMULA. The
problem is in identifying formulas. Every formula starts with an equal sign
but after that it's hard to find another character that is exclusive to a
formula. For example, =A1 is a formula and '=A1 is a text string. Both start
with an equal sign. You might think that you could look for ( ) which *most*
formulas contain but *not all* formulas have ( ) as demonstrated with =A1.

If you might have TEXT strings that start with an equal sign I don't how to
distinguish those from actual formulas. I'm sure it could be done with a
more elaborate UDF but I'm not the best programmer.

--
Biff
Microsoft Excel MVP

Bif,

Thanks for your help..I got it to work on a test (one sheet workbook), but
can't seem to get it to run for all pages in the workbook. Not sure why as
I
copied and pasted the info that you provided.

Essentially, what i want to do is to format cells in 3 ways:
If formula, change font to BLUE
If formula is external reference, color cell Orange
If cell has a number that is hard coded, color cell Green
If cell is blank, leave alone.

Is there a way to combine all of this into the module or cf piece.

thanks again,

-mike


--
Mike

UDF? is this User Defined Function?

Yes

If so, where to i go to set this up?

Let's make a slight change:

Function GetFormula(cell_ref As Range) As String
Application.Volatile
GetFormula = cell_ref.Formula
End Function

Open the VBE editor: ALT F11
Open the Project Explorer: CTRL R
Locate your file name in the pane that opens
Select the file name then right click
Select Insert>Module

Copy/paste the UDF code into the window that opens
Return to Excel: ALT Q

Then apply the cf.


--
Biff
Microsoft Excel MVP


Biff,

Thanks, i think this will work and i have a couple of questions..

UDF? is this User Defined Function? If so, where to i go to set this
up?
Thanks. Sorry to ask what is probably a simple question..if this is a
macro
how would i set it up (as i am not familiar with Macros). If it is
something
else can you let me know?

I appreciate you answering my post and i think this will work, just
need a
little help getting it set up.
--
Mike


:

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 Format>Conditional Formatting
Formula Is:

=AND(LEFT(getformula(A1))="=",SEARCH(".xls",getformula(A1)))

Click the Format button
Select the style(s) desired
OK out

--
Biff
Microsoft Excel MVP


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?

 

[T. Valko]

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP

Thank you..this worked.
--
Mike

It works for me regardless of which sheet the formatting is applied to.

Here are the conditional formatting conditions:

Condition 1
If formula is external reference, color cell Orange
Formula Is:
=AND(LEFT(getformula(A1))="=",SEARCH(".xls",getformula(A1)))

Condition 2
If formula, change font to BLUE
Formula Is:
=LEFT(getformula(A1))="="

Condition 3
If cell has a number that is hard coded, color cell Green
Formula Is:
=AND(LEFT(getformula(A1))<>"=",ISNUMBER(A1))

NB: condition 2 is kind of ambiguous. If you have a TEXT string in a cell
that begins with an equal sign this will be evaluated as a FORMULA. The
problem is in identifying formulas. Every formula starts with an equal
sign
but after that it's hard to find another character that is exclusive to a
formula. For example, =A1 is a formula and '=A1 is a text string. Both
start
with an equal sign. You might think that you could look for ( ) which
*most*
formulas contain but *not all* formulas have ( ) as demonstrated with
=A1.

If you might have TEXT strings that start with an equal sign I don't how
to
distinguish those from actual formulas. I'm sure it could be done with a
more elaborate UDF but I'm not the best programmer.

--
Biff
Microsoft Excel MVP

Bif,

Thanks for your help..I got it to work on a test (one sheet workbook),
but
can't seem to get it to run for all pages in the workbook. Not sure why
as
I
copied and pasted the info that you provided.

Essentially, what i want to do is to format cells in 3 ways:
If formula, change font to BLUE
If formula is external reference, color cell Orange
If cell has a number that is hard coded, color cell Green
If cell is blank, leave alone.

Is there a way to combine all of this into the module or cf piece.

thanks again,

-mike


--
Mike


:

UDF? is this User Defined Function?

Yes

If so, where to i go to set this up?

Let's make a slight change:

Function GetFormula(cell_ref As Range) As String
Application.Volatile
GetFormula = cell_ref.Formula
End Function

Open the VBE editor: ALT F11
Open the Project Explorer: CTRL R
Locate your file name in the pane that opens
Select the file name then right click
Select Insert>Module

Copy/paste the UDF code into the window that opens
Return to Excel: ALT Q

Then apply the cf.


--
Biff
Microsoft Excel MVP


Biff,

Thanks, i think this will work and i have a couple of questions..

UDF? is this User Defined Function? If so, where to i go to set this
up?
Thanks. Sorry to ask what is probably a simple question..if this is
a
macro
how would i set it up (as i am not familiar with Macros). If it is
something
else can you let me know?

I appreciate you answering my post and i think this will work, just
need a
little help getting it set up.
--
Mike


:

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 Format>Conditional Formatting
Formula Is:

=AND(LEFT(getformula(A1))="=",SEARCH(".xls",getformula(A1)))

Click the Format button
Select the style(s) desired
OK out

--
Biff
Microsoft Excel MVP


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?

 

[Renee]

Does anyone know how to do this in Excel 2007? I need basically the same
formatting, i.e. color 1 for links, color 2 for formulas, and color 3 for
hard-keyed
Thanks
Renee

I think i see why this didn't work for the whole workbook and cells..

is it in the conditional formatting, where i reference cell (A1)?
=AND(LEFT(getformula(A1))="=",SEARCH(".xls",getformula(A1)))

if this is the case what to i put in there to make sure all cells are
checked..I clicked on the upper left corner of the worksheet so the whole
worksheet was selected, then applied this cond format..nothing changed...Also
i have this listed as the second condition..I wonder if that is the issue as
well.
--
Mike

UDF? is this User Defined Function?
Yes

If so, where to i go to set this up?

Let's make a slight change:

Function GetFormula(cell_ref As Range) As String
Application.Volatile
GetFormula = cell_ref.Formula
End Function

Open the VBE editor: ALT F11
Open the Project Explorer: CTRL R
Locate your file name in the pane that opens
Select the file name then right click
Select Insert>Module

Copy/paste the UDF code into the window that opens
Return to Excel: ALT Q

Then apply the cf.


--
Biff
Microsoft Excel MVP

Biff,

Thanks, i think this will work and i have a couple of questions..

UDF? is this User Defined Function? If so, where to i go to set this up?
Thanks. Sorry to ask what is probably a simple question..if this is a
macro
how would i set it up (as i am not familiar with Macros). If it is
something
else can you let me know?

I appreciate you answering my post and i think this will work, just need a
little help getting it set up.
--
Mike


:

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 Format>Conditional Formatting
Formula Is:

=AND(LEFT(getformula(A1))="=",SEARCH(".xls",getformula(A1)))

Click the Format button
Select the style(s) desired
OK out

--
Biff
Microsoft Excel MVP


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?