Two font colours in one cell – VBA error

Topic

New Reply

I have a VBA routine (Excel 2010) which tests the font colour in a cell using the expression

Range(“Cell”).Font.ColorIndex

This works fine except when there are two font colours in one cell I get a VBA error:

Run time error ’94’ invalid use of Null

Well that wasn’t totally unexpected so I thought I would trap this condition using the IsError function in VBA

However the expression . . .

IsError(Range(“Cell”).Font.ColorIndex)

. . . returns the value False

So is this an error or not ? The IsError function thinks it isn’t and VBA runtime thinks it is !

How can I test for this two-colour condition without getting a VBA error ?

Thanks for any ideas.

Martin

Reply | Quote

Replies

Hi Martin

You would expect to get a colorIndex of zero for multi-coloured font.

You can only get the ‘index value’ if the entire font-colour within the cell has been set via the 56-colour palette.

If you use RGB values to set your font colour you can have 16,777,216 values for each ‘character’

zeddy

Reply | Quote

…. there’s no “font colour” when there are multiples colours in one cell, but the value returned is not zero, it comes up with an error, even though IsError says there isn’t one.

I am trying to trap and handle  this multiple-clour condition so that the rest of my code can function but I can’t find a way to do so . . .

Martin
PS Good to hear from you

Reply | Quote

Martin,

Could you post the routine for context?

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

[WSMartinM]

Thanks for the response RG.

The whole procedure is huge (1,000+ lines) so I have extracted the issue into a small workbook which is now attached.

• This reply was modified 5 years, 2 months ago by WSMartinM.

Reply | Quote

I’m not sure that worked !
Trying again . . .

Reply | Quote

Hi Martin

I was being lazy.

I was trying to suggest that using .Font.Color or .Font.ColorIndex will both give a zero value when there is multi-coloured text within a cell.

You can get non-zero values for .Font.Color or .Font.ColorIndex even when the cell is empty i.e. has no text within it (since it is a ‘cell’ property).

..so the clue is in the “invalid use of Null” for which we would need more info, as RG suggests.

zeddy

 

Reply | Quote

OK I admit defeat – how do I attach a file into this Forum ?

Reply | Quote

WSMartinM wrote:

how do I attach a file into this Forum ?

This method works for files/attachments, but some spreadsheets (and similar types) don’t get through our firewall because of the scripts contained. In that case, either link a cloud source, or use pastebin.com

Reply | Quote

Hi Martin

..I think you have to attach the file as a zip file in this forum

zeddy

Reply | Quote

I no longer have any zipping capability !
Anyway, the small workbook I created just has one cell with the range name Cell (which contains text of two colours) and one procedure as below. The MsgBox lines are just to track what’s going on:

Sub Test()
Dim Text As String 'The text in "Cell"
Dim FontColour As Integer 'The Font colour in "Cell"
Text = Range("Cell").Value
MsgBox Text
MsgBox IsError(Range("Cell").Font.ColorIndex)
FontColour = Range("Cell").Font.ColorIndex
MsgBox FontColour
End Sub

Reply | Quote

Then you might need to test for Null then..

MsgBox IsNull(Range(“Cell”).Font.ColorIndex)

zeddy

1 user thanked author for this post.

RetiredGeek

Reply | Quote

Thanks Zeddy, sorted.

Why can’t I see what’s staring me in the face ?

Cheers,

Martin

Reply | Quote