Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Apr 2015
    Posts
    153
    Thanks
    70
    Thanked 0 Times in 0 Posts

    Code to color row if two conditions are meant

    I don't want to wear out my welcome by asking too many questions while I learn VBA. However, after searching google for hours I decided to contact the experts who have been so kind and helpful.

    The code below works well, but how can it be modified to color a row in a range when two condition are meant. The conditions are that the row have a 2 and a 3, then color it the same color.

    I included a file to help.

    Thanks in advance for any help provided.


    Sub Cell_Color()
    Dim ws As Worksheet
    Dim c As Range


    Set ws = ActiveSheet


    'Colors cells with 2 red and cells with 3 yellow. Empty cells, includes 0, no color.
    For Each c In ActiveSheet.Range("b2:k336")
    If c.Value = "2" Then c.Interior.ColorIndex = 3
    If c.Value = "3" Then c.Interior.ColorIndex = 6
    If c.Value = "" Then c.Interior.ColorIndex = 4
    Next c


    End Sub
    Attached Files Attached Files
    Last edited by Excelnewbie; 2015-04-24 at 17:52.

  2. #2
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,640
    Thanks
    115
    Thanked 651 Times in 593 Posts
    Excel,

    This can easily be done with conditional formatting instead of vba. In you example, the conditional formula is:
    =AND(COUNTIF($B2:$K2,2)>=1,COUNTIF($B2:$K2,3)>=1)

    The range formula is:
    =$B$2:$K$336

    Color_Excel.png

    If it can have only one of each then change the >= to just =

    HTH,
    Maud
    Attached Files Attached Files

  3. The Following User Says Thank You to Maudibe For This Useful Post:

    Excelnewbie (2015-04-24)

  4. #3
    2 Star Lounger
    Join Date
    Apr 2015
    Posts
    153
    Thanks
    70
    Thanked 0 Times in 0 Posts
    Maudibe-

    Thanks again for your help! Thanks for teaching.

  5. #4
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,640
    Thanks
    115
    Thanked 651 Times in 593 Posts
    Excel,

    To follow through with you original request using vba, this would be the equivalent:

    in a standard module:
    Code:
    Public Sub ColorRow()
    For I = 2 To 336
        If WorksheetFunction.CountIf(Range("B" & I & ":K" & I), 2) >= 1 And _
           WorksheetFunction.CountIf(Range("B" & I & ":K" & I), 3) >= 1 Then
               Range("B" & I & ":K" & I).Interior.Color = vbRed
        End If
    Next I
    End Sub

  6. #5
    2 Star Lounger
    Join Date
    Apr 2015
    Posts
    153
    Thanks
    70
    Thanked 0 Times in 0 Posts
    Maudibe-

    Thanks again! As an Excel and VBA beginner this code helps me clearly see how useful a variable is.

Posting Permissions

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