News, tips, advice, support for Windows, Office, PCs & more. Tech help. No bull. We're community supported by donations from our Plus Members, and proud of it
Home icon Home icon Home icon Email icon RSS icon
  • Two font colours in one cell – VBA error

    Posted on WSMartinM Comment on the AskWoody Lounge

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

    Topic Resolution: Resolved

    Tagged: 

    Viewing 11 reply threads
    • Author
      Posts
      • #2141493 Reply
        WSMartinM
        AskWoody Lounger

        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

      • #2141512 Reply
        zeddy
        AskWoody_MVP

        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

      • #2141515 Reply
        WSMartinM
        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 . . .

        Martin
        PS Good to hear from you

      • #2141516 Reply
        RetiredGeek
        AskWoody MVP

        Martin,

        Could you post the routine for context?

        May the Forces of good computing be with you!

        RG

        PowerShell & VBA Rule!
        Computer Specs

      • #2141523 Reply
        WSMartinM
        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 month, 1 week ago by WSMartinM.
      • #2141524 Reply
        WSMartinM
        AskWoody Lounger

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

      • #2141525 Reply
        zeddy
        AskWoody_MVP

        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

         

      • #2141526 Reply
        WSMartinM
        AskWoody Lounger

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

        • #2141623 Reply
          Kirsty
          Da Boss

          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

      • #2141527 Reply
        zeddy
        AskWoody_MVP

        Hi Martin

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

        zeddy

      • #2141530 Reply
        WSMartinM
        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 Reply
        zeddy
        AskWoody_MVP

        Then you might need to test for Null then..

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

        zeddy

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

        Thanks Zeddy, sorted.

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

        Cheers,

        Martin

    Viewing 11 reply threads

    Please follow the -Lounge Rules- no personal attacks, no swearing, and politics/religion are relegated to the Rants forum.

    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.