conditional formatting links

N

Newbie

Hi,

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

Thanks.
 
G

Gary''s Student

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

Newbie

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!
 
N

Newbie

LOVE IT!!!! thank you very much.

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

Newbie

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

Gary''s Student

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!
 
N

Newbie

Thank you very much - does the trick just fine.

Gary''s Student said:
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 said:
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.
Click to expand...
Click to expand...
 
Top