Determine if a linked cell contains data

[Steve Bentley]

Using vba I'd like to change the formatting/colours on a particular cell
if the cell contains text.

So for example:

If Application.WorksheetFunction.IsText(Cells(i, ColNum)) Then
Cells(i, ColNum).Font.Bold = True
Cells(i, ColNum).Interior.ColorIndex = 40
End If

This works fine for cells which do contain text but not for cells which
contain 'linked text'. I have some cells which are linked from a 2nd
sheet and in the 2nd sheet they contain text.

The code above does not work because there the cell contains ='Sheet2'!B93

How can I get the results I'm needing?

Thanks
Steve

 

[Ken Johnson]

Using vba I'd like to change the formatting/colours on a particular cell
if the cell contains text.

So for example:

If Application.WorksheetFunction.IsText(Cells(i, ColNum)) Then
Cells(i, ColNum).Font.Bold = True
Cells(i, ColNum).Interior.ColorIndex = 40
End If

This works fine for cells which do contain text but not for cells which
contain 'linked text'. I have some cells which are linked from a 2nd
sheet and in the 2nd sheet they contain text.

The code above does not work because there the cell contains ='Sheet2'!B93

How can I get the results I'm needing?

Thanks
Steve

With my xl2003 your code works properly, ie linked text is recognised
as text and the formatting is applied.
What is returned by ISTEXT(Sheet2!B93) in any cell on the activesheet?

I did add...

Else: Cells(i, ColNum).Font.Bold = False
Cells(i, ColNum).Interior.ColorIndex = 0

to the If statement so that the formatting for text would be removed
in the event of it already being present, but I doubt that that would
be the solution to your problem.

Ken Johnson

 

[Bentley.Steve1]

With my xl2003 your code works properly, ie linked text is recognised
as text and the formatting is applied.
What is returned by ISTEXT(Sheet2!B93) in any cell on the activesheet?

I did add...

Else: Cells(i, ColNum).Font.Bold = False
Cells(i, ColNum).Interior.ColorIndex = 0

to the If statement so that the formatting for text would be removed
in the event of it already being present, but I doubt that that would
be the solution to your problem.

Ken Johnson

Hi,
Sorry I had some conditional formatting already enabled for these
cells. Once I removed this then the code also works fine.
Sorry for this.

Steve

 

[Ken Johnson]

Hi,
Sorry I had some conditional formatting already enabled for these
cells. Once I removed this then the code also works fine.
Sorry for this.

Steve

Hi Steve,

The old forgotten Conditional Formatting trap!
I've fallen in that one myself.
Nice to know things are now OK.

Ken Johnson