Determine if a linked cell contains data

  • Thread starter Thread starter Steve Bentley
  • Start date Start date
S

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
 
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
Click to expand...

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
 
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
Click to expand...

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,
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
Click to expand...

Hi Steve,

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

Ken Johnson
 
Back
Top