News, tips, advice, support for Windows, Office, PCs & more
Home icon Home icon Home icon Email icon RSS icon

We're community supported and proud of it!

  • Two font colours in one cell – VBA error

    Home Forums AskWoody support Productivity software by function MS Excel and spreadsheet help Two font colours in one cell – VBA error

    Topic Resolution: Resolved


    Viewing 11 reply threads
    • Author
      • #2141493
        AskWoody Lounger

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


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


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


      • #2141512

        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’


      • #2141515
        AskWoody Lounger

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

        PS Good to hear from you

      • #2141516
        AskWoody MVP


        Could you post the routine for context?

        May the Forces of good computing be with you!


        PowerShell & VBA Rule!
        Computer Specs

      • #2141523
        AskWoody Lounger

        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 1 year, 5 months ago by WSMartinM.
      • #2141524
        AskWoody Lounger

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

      • #2141525

        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). the clue is in the “invalid use of Null” for which we would need more info, as RG suggests.



      • #2141526
        AskWoody Lounger

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

        • #2141623

          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

      • #2141527

        Hi Martin

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


      • #2141530
        AskWoody Lounger

        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
      • #2141534

        Then you might need to test for Null then..

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


        1 user thanked author for this post.
      • #2141548
        AskWoody Lounger

        Thanks Zeddy, sorted.

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



    Viewing 11 reply threads

    Please follow the -Lounge Rules- no personal attacks, no swearing, no politics or religion.

    Reply To: Two font colours in one cell – VBA error

    You can use BBCodes to format your content.
    Your account can't use Advanced BBCodes, they will be stripped before saving.