Hi,

Is there a way to conditionally format cells that contain either a local
link, external link or typed value?

Thanks.

You can apply conditional formatting just as in an un-linked cell. The
formatting (conditional or un-conditional) does not link directly:

If A1 contains =Z100, then A1 will display the value of Z100, not its format.

If this is what you want to accomplish, then copy Z100 and
paste/special/format into A1.
--
Gary''s Student


"Newbie" wrote:

Hi,

Is there a way to conditionally format cells that contain either a local
link, external link or typed value?

Thanks.

Thanks for the reply. I was really after highlighting cells that contain
links. I have tried:
left(a1,1)="="
but doesn't seem to work!

"Gary''s Student" wrote:

You can apply conditional formatting just as in an un-linked cell. The
formatting (conditional or un-conditional) does not link directly:

If A1 contains =Z100, then A1 will display the value of Z100, not its format.

If this is what you want to accomplish, then copy Z100 and
paste/special/format into A1.
--
Gary''s Student


"Newbie" wrote:

Hi,

Is there a way to conditionally format cells that contain either a local
link, external link or typed value?

Thanks.

First enter this tiny UDF:

Function isformula(r As Range) As Boolean
isformula = r.HasFormula
End Function

Then format your cell (A1, for example):
conditional format/formula is/=isformula(A1) and set the format

For info on UDFs, see:


http://www.cpearson.com/excel/differen.htm
--
Gary's Student


"Newbie" wrote:

Thanks for the reply. I was really after highlighting cells that contain
links. I have tried:
left(a1,1)="="
but doesn't seem to work!

"Gary''s Student" wrote:

You can apply conditional formatting just as in an un-linked cell. The
formatting (conditional or un-conditional) does not link directly:

If A1 contains =Z100, then A1 will display the value of Z100, not its format.

If this is what you want to accomplish, then copy Z100 and
paste/special/format into A1.
--
Gary''s Student


"Newbie" wrote:

Hi,

Is there a way to conditionally format cells that contain either a local
link, external link or typed value?

Thanks.

LOVE IT!!!! thank you very much.

..... is it possible to develop the UDF further to differentiate external
links?



"Gary''s Student" wrote:

First enter this tiny UDF:

Function isformula(r As Range) As Boolean
isformula = r.HasFormula
End Function

Then format your cell (A1, for example):
conditional format/formula is/=isformula(A1) and set the format

For info on UDFs, see:


http://www.cpearson.com/excel/differen.htm
--
Gary's Student


"Newbie" wrote:

Thanks for the reply. I was really after highlighting cells that contain
links. I have tried:
left(a1,1)="="
but doesn't seem to work!

"Gary''s Student" wrote:

You can apply conditional formatting just as in an un-linked cell. The
formatting (conditional or un-conditional) does not link directly:

If A1 contains =Z100, then A1 will display the value of Z100, not its format.

If this is what you want to accomplish, then copy Z100 and
paste/special/format into A1.
--
Gary''s Student


"Newbie" wrote:

Hi,

Is there a way to conditionally format cells that contain either a local
link, external link or typed value?

Thanks.

Sure. Just let us know what you need.
--
Gary''s Student


"Newbie" wrote:

LOVE IT!!!! thank you very much.

.... is it possible to develop the UDF further to differentiate external
links?



"Gary''s Student" wrote:

First enter this tiny UDF:

Function isformula(r As Range) As Boolean
isformula = r.HasFormula
End Function

Then format your cell (A1, for example):
conditional format/formula is/=isformula(A1) and set the format

For info on UDFs, see:


http://www.cpearson.com/excel/differen.htm
--
Gary's Student


"Newbie" wrote:

Thanks for the reply. I was really after highlighting cells that contain
links. I have tried:
left(a1,1)="="
but doesn't seem to work!

"Gary''s Student" wrote:

You can apply conditional formatting just as in an un-linked cell. The
formatting (conditional or un-conditional) does not link directly:

If A1 contains =Z100, then A1 will display the value of Z100, not its format.

If this is what you want to accomplish, then copy Z100 and
paste/special/format into A1.
--
Gary''s Student


"Newbie" wrote:

Hi,

Is there a way to conditionally format cells that contain either a local
link, external link or typed value?

Thanks.

thanks *1m
I would like cell fill to be:
green for links or calculations from sources on the same sheet,
blue for links to a different sheet, but in the same book
and red for links to a different book ..

appreciate your help.


"Gary''s Student" wrote:

Sure. Just let us know what you need.
--
Gary''s Student


"Newbie" wrote:

LOVE IT!!!! thank you very much.

.... is it possible to develop the UDF further to differentiate external
links?



"Gary''s Student" wrote:

First enter this tiny UDF:

Function isformula(r As Range) As Boolean
isformula = r.HasFormula
End Function

Then format your cell (A1, for example):
conditional format/formula is/=isformula(A1) and set the format

For info on UDFs, see:


http://www.cpearson.com/excel/differen.htm
--
Gary's Student


"Newbie" wrote:

Thanks for the reply. I was really after highlighting cells that contain
links. I have tried:
left(a1,1)="="
but doesn't seem to work!

"Gary''s Student" wrote:

You can apply conditional formatting just as in an un-linked cell. The
formatting (conditional or un-conditional) does not link directly:

If A1 contains =Z100, then A1 will display the value of Z100, not its format.

If this is what you want to accomplish, then copy Z100 and
paste/special/format into A1.
--
Gary''s Student


"Newbie" wrote:

Hi,

Is there a way to conditionally format cells that contain either a local
link, external link or typed value?

Thanks.

A new UDF:

Function isformula(r As Range) As Integer
Dim s As String
isformula = 0
If Not r.HasFormula Then Exit Function

isformula = 1
s = r.Formula
If InStr(1, s, "[") < 0 Then
isformula = 3
Exit Function
End If
If InStr(1, s, "!") < 0 Then
isformula = 2
End If

End Function

junk the old one

Then format your cell (A1 for example):
conditional format/formula is/=isformula(A1)=1 and set the format to green
conditional format/formula is/=isformula(A1)=2 and set the format to blue
conditional format/formula is/=isformula(A1)=3 and set the format to red


WARNING!!

This UDF is not bullet-proof!! It assumes that ! is only for off-sheet
refs. and that [ is only for off-book refs. So it can be fooled!
--
Gary's Student


"Newbie" wrote:

thanks *1m
I would like cell fill to be:
green for links or calculations from sources on the same sheet,
blue for links to a different sheet, but in the same book
and red for links to a different book ..

appreciate your help.


"Gary''s Student" wrote:

Sure. Just let us know what you need.
--
Gary''s Student


"Newbie" wrote:

LOVE IT!!!! thank you very much.

.... is it possible to develop the UDF further to differentiate external
links?



"Gary''s Student" wrote:

First enter this tiny UDF:

Function isformula(r As Range) As Boolean
isformula = r.HasFormula
End Function

Then format your cell (A1, for example):
conditional format/formula is/=isformula(A1) and set the format

For info on UDFs, see:


http://www.cpearson.com/excel/differen.htm
--
Gary's Student


"Newbie" wrote:

Thanks for the reply. I was really after highlighting cells that contain
links. I have tried:
left(a1,1)="="
but doesn't seem to work!

"Gary''s Student" wrote:

You can apply conditional formatting just as in an un-linked cell. The
formatting (conditional or un-conditional) does not link directly:

If A1 contains =Z100, then A1 will display the value of Z100, not its format.

If this is what you want to accomplish, then copy Z100 and
paste/special/format into A1.
--
Gary''s Student


"Newbie" wrote:

Hi,

Is there a way to conditionally format cells that contain either a local
link, external link or typed value?

Thanks.

Thank you very much - does the trick just fine.

"Gary''s Student" wrote:

A new UDF:

Function isformula(r As Range) As Integer
Dim s As String
isformula = 0
If Not r.HasFormula Then Exit Function

isformula = 1
s = r.Formula
If InStr(1, s, "[") < 0 Then
isformula = 3
Exit Function
End If
If InStr(1, s, "!") < 0 Then
isformula = 2
End If

End Function

junk the old one

Then format your cell (A1 for example):
conditional format/formula is/=isformula(A1)=1 and set the format to green
conditional format/formula is/=isformula(A1)=2 and set the format to blue
conditional format/formula is/=isformula(A1)=3 and set the format to red


WARNING!!

This UDF is not bullet-proof!! It assumes that ! is only for off-sheet
refs. and that [ is only for off-book refs. So it can be fooled!
--
Gary's Student


"Newbie" wrote:

thanks *1m
I would like cell fill to be:
green for links or calculations from sources on the same sheet,
blue for links to a different sheet, but in the same book
and red for links to a different book ..

appreciate your help.


"Gary''s Student" wrote:

Sure. Just let us know what you need.
--
Gary''s Student


"Newbie" wrote:

LOVE IT!!!! thank you very much.

.... is it possible to develop the UDF further to differentiate external
links?



"Gary''s Student" wrote:

First enter this tiny UDF:

Function isformula(r As Range) As Boolean
isformula = r.HasFormula
End Function

Then format your cell (A1, for example):
conditional format/formula is/=isformula(A1) and set the format

For info on UDFs, see:


http://www.cpearson.com/excel/differen.htm
--
Gary's Student


"Newbie" wrote:

Thanks for the reply. I was really after highlighting cells that contain
links. I have tried:
left(a1,1)="="
but doesn't seem to work!

"Gary''s Student" wrote:

You can apply conditional formatting just as in an un-linked cell. The
formatting (conditional or un-conditional) does not link directly:

If A1 contains =Z100, then A1 will display the value of Z100, not its format.

If this is what you want to accomplish, then copy Z100 and
paste/special/format into A1.
--
Gary''s Student


"Newbie" wrote:

Hi,

Is there a way to conditionally format cells that contain either a local
link, external link or typed value?

Thanks.