1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User


  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Add array to loop

Discussion in 'VBA Macros' started by jassybun, Feb 7, 2019.

  1. jassybun

    jassybun Member

    this formula works -but I have multiple text words that need to be red in addition to "late".

    How can I add more words?

    Code (vb):

    Public Sub ChgTxtColor()
      Set myRange = Range("A25:A100")  'The Range that contains the substring you want to change color
     substr = "late"  'The text you want to change color
     txtColor = 3  'The ColorIndex which repsents the color you want to change

      For Each myString In myRange
      lenstr = Len(myString)
      lensubstr = Len(substr)
      For i = 1 To lenstr
      tempString = Mid(myString, i, lensubstr)
      If tempString = substr Then
      myString.Characters(Start:=i, Length:=lensubstr).Font.ColorIndex = txtColor
      End If
      Next i
      Next myString
    End Sub

    Attached Files:

    Last edited by a moderator: Feb 7, 2019
  2. Luke M

    Luke M Excel Ninja

    Let's make one sub that takes arguments, and then we can call it multiple times.

    Code (vb):

    Public Sub MainCall()
        Application.ScreenUpdating = False
        'What are all the things to find?
       Call ChgTxtColor(Range("A25:A100"), "late", 3)
        Call ChgTxtColor(Range("A25:A100"), "start", 3)
        Call ChgTxtColor(Range("A25:A100"), "Chandoo", 3)
        Application.ScreenUpdating = True
    End Sub
    Private Sub ChgTxtColor(myRange As Range, subStr As String, txtColor As Long)
        Dim c As Range
        Dim myString As String
        Dim lenStr As Long
        Dim lenSubStr As Long
        Dim i As Long
        Dim xFound As Long
        Dim boolStatus As Boolean
        boolStatus = Application.ScreenUpdating
        Application.ScreenUpdating = False
        For Each c In myRange
            myString = c.Value
            lenStr = Len(myString)
            'Check for words, one after another
           If InStr(1, myString, subStr) Then
                'Where is the word
               xFound = InStr(1, myString, subStr)
                'How long is it?
               lenSubStr = Len(subStr)
                c.Characters(Start:=xFound, Length:=lenSubStr).Font.ColorIndex = txtColor
            End If
        Next c
        Application.ScreenUpdating = boolStatus
    End Sub
  3. jassybun

    jassybun Member

    I didn't think about this before- but what if I want a string of words and not just one word? I thought this would work for either but it's not
  4. Luke M

    Luke M Excel Ninja

    Not a problem, just list out the phrase you want

    Code (vb):

    Call ChgTxtColor(Range("A25:A100"), "a string of words", 3)
    Peter Bartholomew likes this.
  5. jassybun

    jassybun Member

    thank you so much! you are a life saver...

Share This Page