Conditional Format for External References

D

Dave M

I'd like to use conditional formatting to highlight cells containing
external references.

Nothing too tricky, ie just testing whether first 4 characters in the
formula in the cell are ='C:

I can't seem to work out how to do it.

Any ideas greatly appreciated.

Regards,

Dave
 
B

Bob Phillips

Dave,

In CF, use Formula Is with a formula of

=LEFT(A1,2)="C:"

format as required

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
R

Ramana Murthy

No, Bob ..

The conditional formatting is checking for the value in the cell and not
for the underlying formula. As such, the method suggested by you is not
working ( ...though I also tried it the same way ...<g>)

Regards,
Murthy
 
B

Bob Phillips

That shouldn't make any difference, the test should still find the value. I
didn't include a leading apostrophe (') as per your example. Is that it?

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
R

Ramana Murthy

Bob,

I tried with apostrophe, as well as without it. It doesn't work. Did you
test it yourself?

Regards,
Murthy
 
B

Bob Phillips

Yeah I did.

Why not send me your workbook direct to look at?

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
E

Earl Kiosterud

Bob,

If cell A1 contains the link:
='C:\[Book3.xls]Sheet1'!$A$5

and cell A5 of that Book3.xls contains abcd, then then the following formula
will return abcd, as Ramana says, not =C:\. I think the OP wants the
latter.

=left(A1, 4)
 
E

Earl Kiosterud

Dave,

Paste the following User Defined Function into a general module:

Function GetFormula(RefCell As Range) As String
GetFormula = RefCell.Formula
End Function

Then use it in a cell

=GetFormula(A1)
=LEFT(GetFormula(A1), 3)

It should return ='C: as appropriate. And you can use it in conditional
formatting:

=LEFT(GetFormula(A1),4)="='C:"
 
D

Dave M

Earl,

Thanks, it worked a treat.

Here's an interesting observation...

I used your method, but also decided it would be nice if I could turn
off the highlighting if I wished. Therefore I have a cell in my
spreadsheet with named-range "Show_Links" it can take value either
TRUE or FALSE and is set by a linked CheckBox control which sits on
the worksheet. The idea being the user can turn highlighting on or
off by checking or unchecking the "Highlight Links" box.

Therefore I set my conditional formatting formula to be

=AND(LEFT(GetFormula(A1),4)="='C:",Show_Links)

ie if they're both true, then apply the highlighting.

However I could not get this to work...after hours of pain I stumbled
across the fact that it would work if I reversed the order of the
conditions in the AND function. ie

=AND(Show_Links,LEFT(GetFormula(A1),4)="='C:")

No idea why this is, I wouldn't have thought this would make a
difference.

PS I've only tried this with XL97.
 
E

Earl Kiosterud

Dave,

I got the same results using XP.

The same formula, pasted into a column, correctly yielded TRUE or FALSE for
rows that did or didn't contain the ='C: string when the Show_Links switch
was on. Interestingly, if you don't use a range name (SHow_Link), but a
cell reference instead ($E$1), it works.
 
Top