Consulting

Results 1 to 15 of 15

Thread: How can one VBA search for a font with RGB(xxx, xxx, xxx) values

  1. #1
    VBAX Regular
    Joined
    Mar 2020
    Posts
    79
    Location

    How can one VBA search for a font with RGB(xxx, xxx, xxx) values

    Some fonts used in documents can be off the standard ones. I have tried searching for a font with RGB(xxx, xxx, xxx) values, but VBA did not like it. Is there some intermediate conversion to do....
    How can I proceed best.

  2. #2
    Knowledge Base Approver VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    4,435
    Location
    Kindly post the code you tried...
    Cheers
    Paul Edstein
    [Fmr MS MVP - Word]

  3. #3
    VBAX Regular
    Joined
    Mar 2020
    Posts
    79
    Location
    I gave up on it and deleted it....

    I think it was something like this, in a selection find


    .Font.TextColor.RGB = RGB(68, 114, 196)
    and
    .Font.TextColor = RGB(68, 114, 196)

  4. #4
    VBAX Regular
    Joined
    Mar 2020
    Posts
    79
    Location
    With selection
        Set RngFnd = .Range
        With .Range
            With .Find
                .ClearFormatting
                .Replacement.ClearFormatting
                .text = ""
                .Replacement.text = ""
                .Forward = True
                .Format = False
                .Wrap = wdFindStop
                .MatchWildcards = True
                .Font.Bold = True
                If fontValueFromCombo = "No Color Just Bold" Then
                    .ClearFormatting
    
    
                    .Font.Bold = True
                Else
                    .ClearFormatting
            ' This was where i tried them *****       .Font.ColorIndex = FontColor
            ' This was where i tried them ******       .Font.TextColor.RGB = RGB(68, 114, 196)
                    .Font.Bold = True
                End If
                .Font.Size = ComboBox5LinkFontSize.Value
                .Execute
            End With
            If InStr(selection, "link>") Then
                MsgBox ("Selection already contains <tlink> or <link>, will exit macro")
                Exit Sub
            End If
            Do While .Find.Found
                If .InRange(RngFnd) Then
                    If .Paragraphs.Count > 1 Then .Start = .Paragraphs(1).Range.End
                    If .Start = .Paragraphs(1).Range.Start Then
                        StrTxt = .text
                        .InsertBefore "<link>" & Trim(Split(StrTxt, vbCr)(0)) & "</link>"
                        .Start = .End - Len(StrTxt)
                    End If
                End If
                .Collapse wdCollapseEnd
                .Find.Execute
            Loop
    
    
        End With
    End With
    RngFnd.Select

  5. #5
    Knowledge Base Approver VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    4,435
    Location
    You might get better results if you didn't invent your own uses for properties (e.g. .TextColor). Even the macro recorder would have given you the correct property to use for an RGB colour.
    Last edited by macropod; 03-17-2020 at 03:59 PM.
    Cheers
    Paul Edstein
    [Fmr MS MVP - Word]

  6. #6
    VBAX Regular
    Joined
    Mar 2020
    Posts
    79
    Location
    I must have misunderstood this from other site you frequent.

    https://stackoverflow.com/questions/...ont-colorindex

  7. #7
    Knowledge Base Approver VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    4,435
    Location
    The mere fact I frequent StackOverflow doesn't mean I endorse everything you might read there, especially when the discussion in the thread you cited clearly shows that .TextColor does not work this way! If you looked at .TextColor in the VBA help file, you'd find it's a Read-only property.
    Cheers
    Paul Edstein
    [Fmr MS MVP - Word]

  8. #8
    VBAX Regular
    Joined
    Mar 2020
    Posts
    79
    Location
    So going the macro record route I get another invented property, or at least one that does not show on

    IntelliSense
    selection.Find.Font.Color = -721354906

    I thought I was doing a read only action? I want to find a piece of text of that colour.

    So what is the best route?

  9. #9
    Knowledge Base Approver VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    4,435
    Location
    Quote Originally Posted by JPG View Post
    So going the macro record route I get another invented property, or at least one that does not show on
    IntelliSense
    selection.Find.Font.Color = -721354906
    '.Color' is not 'another invented property'. The '-721354906' is simply the numeric equivalent of an RGB conversion. So, for your RGB(68, 114, 196), use:
    .Find.Font.Color = RGB(68, 114, 196)
    Cheers
    Paul Edstein
    [Fmr MS MVP - Word]

  10. #10
    VBAX Regular
    Joined
    Mar 2020
    Posts
    79
    Location
    Thanks, but at the moment it does not work. I will try again in the morning.

  11. #11
    The following works

    Sub Macro1()
    Dim oRng As Range
        Set oRng = ActiveDocument.Range
        With oRng.Find
            .ClearFormatting
            .Replacement.ClearFormatting
            .Text = ""
            .Font.Color = RGB(68, 114, 196)
            .Replacement.Text = ""
            Do While .Execute()
                oRng.Select
                MsgBox oRng.Text
                oRng.Collapse 0
            Loop
        End With
        Set oRng = Nothing
    End Sub
    Graham Mayor - MS MVP (Word) 2002-2019
    Visit my web site for more programming tips and ready made processes
    http://www.gmayor.com

  12. #12
    VBAX Regular
    Joined
    Mar 2020
    Posts
    79
    Location
    I appear to have been testing with the only color that did not work with this. It appears that it is a theme color as it is the only one that changes as I mouse over themes. It is a negative number also.
    The challenge is to overcome this some how. At the present time I just popup a message to change to a non theme color.

    For your test above try the settings for a color which looks like salmon pink RGB(251, 212, 180) set that to some text in Word document and run your test. It does not find it in Word 2010 I can assure you. If you are using Office 365 and it works, then something has changed.

    Also tried to use this to get the info. Got the info for the font, but still not working, though the code was working.

    I notice that only these standard colors work, at least in 2010 with my testing.

    0001 Jon theWord_2020-03-18_112137.jpg


    Bottom line. It would be interesting to know if this works in the example doc in 365.

    Thank you for your assistance in this matter. I will be paying it forward.

    Jon

  13. #13
    VBAX Regular
    Joined
    Mar 2020
    Posts
    79
    Location
    Update. I got it working for the theme colors also. So good result.
    I think my issue was in part that one of the words was a slightly different color and so when I tried to tag them there was not a match. Lesson learned, but it has been a useful process.
    Thanks also to Paul for keeping me on my toes

    I will leave the above post there just for my own embarrassment.

    The other part of the issue was that you need to use

    col = selection.Font.Fill.ForeColor
    What I then do is make the commandButton22FontColor.BackColor = col then use this in the search

    to get the correct data and not a negative number.

  14. #14
    The code last posted works for me in Word 2010 and 2016 - provided of course the font is actually formatted with RGB(251, 212, 180) and you make the change in the macro. See https://www.youtube.com/watch?v=HG_gzSHvgeM
    Graham Mayor - MS MVP (Word) 2002-2019
    Visit my web site for more programming tips and ready made processes
    http://www.gmayor.com

  15. #15
    VBAX Regular
    Joined
    Mar 2020
    Posts
    79
    Location
    Thanks you are correct. It was a part of my code I had moved that caused an issue. I think I got it sorted again. Thanks for the video showing it working.


    Gone wacky again on custom fonts. I will have to sleep on it.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •