Announcement

Collapse
No announcement yet.

Color Rows Differently For Each Keyword In Column

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Color Rows Differently For Each Keyword In Column



    I need to run a macro to start a search in H3 (and continue down the column) and search for one of many words, let's say word1, word2,word3,word4,word5,word6,word7,word8,word9,and word10.
    depending on the word that is found, color the entire row. colors don't matter as long as they are different.
    $10

  • #2
    Re: Color Rows Differently For Each Keyword In Column

    Try the code below after modification of needed words;
    Code:
    Sub ColorRows()
        Dim rCell As Range
        Dim lColor As Long
        Dim lCheck As Long
        Dim WordList()
         
        On Error Resume Next
        lColor = 1
         'Add/modify words in Array
        WordList = Array("word1", "word2", "Word3", "word4")
        With WorksheetFunction
            For Each rCell In Range("H1", Cells(Rows.Count, "H").End(xlUp))
                lCheck = 0
                lCheck = .Match(rCell, WordList, 0)
                    Select Case lCheck
                       Case 1: rCell.EntireRow.Interior.ColorIndex = 2
                       Case 2: rCell.EntireRow.Interior.ColorIndex = 3
                       Case 3: rCell.EntireRow.Interior.ColorIndex = 4
                       Case 4: rCell.EntireRow.Interior.ColorIndex = 5
                       'Follow pattern
                       
                       Case Else
                       'Do nothing
                    End Select
            Next rCell
        End With
        On Error GoTo 0
    End Sub
    Last edited by Dave Hawley; October 29th, 2008, 08:03.

    Comment


    • #3
      Re: Color Rows Differently For Each Keyword In Column

      I need the rows to be the same color if the same word is found. This colors each row a different color.

      Comment


      • #4
        Re: Color Rows Differently For Each Keyword In Column

        Hi

        You could use a custom function like presented below.

        Code:
        Sub ColorRows()
            Dim rCell As Range
            Dim lColor As Long
            Dim lCheck As Long
            Dim WordList()
             
            On Error Resume Next
            lColor = 1
             'Add/modify words in Array
            WordList = Array("word1", "word2", "Word3", "word4")
            With WorksheetFunction
                For Each rCell In Range("H1", Cells(Rows.Count, "H").End(xlUp))
                    lCheck = 0
                    lCheck = .Match(rCell, WordList, 0)
                    If lCheck > 0 Then
                        rCell.EntireRow.Interior.ColorIndex = GetColor(lCheck)
                    End If
                Next rCell
            End With
            On Error GoTo 0
        End Sub
        
        Function GetColor(lNumberItem As Long) As Integer
        
            Select Case lNumberItem
            
                Case 1: GetColor = 3
                Case 2: GetColor = 6
                Case 3: GetColor = 16
                Case 4: GetColor = 5
                
                'case ...
                
            End Select
        
        End Function
        The Choose function is an alternative if you want to condense the notation.

        Wigi
        Regards,

        Wigi

        Excel MVP 2011-2014

        For more Excel memes: visit http://www.wimgielis.com ==> English articles ==> Excel memes

        -- Topics without [CODE] tags or a meaningful topic title, will be neglected by me (and probably many others as well) --

        Comment


        • #5
          Re: Color Rows Differently For Each Keyword In Column

          compile error at:

          GetColor(lCheck)

          Comment


          • #6
            Re: Color Rows Differently For Each Keyword In Column

            nevermind. my error. not a compile error

            Comment


            • #7
              Re: Color Rows Differently For Each Keyword In Column

              Try my code now, it's been edited. Pay Wigi the $10.00.

              Comment


              • #8
                Re: Color Rows Differently For Each Keyword In Column

                Follow-up question by Peyton by means of PM.

                wigi-

                I would like to use the following code, but with wildcards, if possible.

                <from your previous code>
                WordList = Array("word1", "word2", "word3", "word4")

                to something like

                WordList = Array("*word1*", "*word2*", "*word3*", "*word4*")
                Regards,

                Wigi

                Excel MVP 2011-2014

                For more Excel memes: visit http://www.wimgielis.com ==> English articles ==> Excel memes

                -- Topics without [CODE] tags or a meaningful topic title, will be neglected by me (and probably many others as well) --

                Comment


                • #9
                  Re: Color Rows Differently For Each Keyword In Column

                  You need to change the code to achieve this. The Like operator seems to be a logical choice for this:

                  Code:
                  Sub bisColorRows()
                      Dim rCell As Range
                      For Each rCell In Range("H1", Cells(Rows.Count, "H").End(xlUp))
                  
                          Select Case True
                              Case rCell Like "*word1*": rCell.EntireRow.Interior.ColorIndex = 2
                              Case rCell Like "*word2*": rCell.EntireRow.Interior.ColorIndex = 3
                              Case rCell Like "*word3*": rCell.EntireRow.Interior.ColorIndex = 4
                              Case rCell Like "*word4*": rCell.EntireRow.Interior.ColorIndex = 5
                              Case Else
                                  'Do nothing
                          End Select
                      Next rCell
                  End Sub
                  Wigi
                  Regards,

                  Wigi

                  Excel MVP 2011-2014

                  For more Excel memes: visit http://www.wimgielis.com ==> English articles ==> Excel memes

                  -- Topics without [CODE] tags or a meaningful topic title, will be neglected by me (and probably many others as well) --

                  Comment


                  • #10


                    Re: Color Rows Differently For Each Keyword In Column

                    Thanks, Wigi.

                    Comment

                    Working...
                    X