none
VBA code to color code cell based on conditions from 3 columns RRS feed

  • Question

  • Hello VBA experts. Novice on VBA here. Need your help on updating this code that I wrote. Basically, its looking at multiple columns. If it shows "RAIL" on Col B and shows either "France" or "Germany" on Col C and col O shows equal to or more than 77 (in days, this is numeric format), that particular cell on Col O will turn red. Right now, its not doing the job to color code the cells with >= 77. 
    Appreciate in advance all the help or recommendations!

    'Color code if it shows the ff results
    'Col B: RAIL, Col C: either France or Germany, Col O: IF => 77 DAYS

     
        Dim rng As Range
        Dim rcell As Range
        Dim lr As Long
        lr = Range("B" & Rows.count).End(xlUp).row
        Set rng = Range("B1:B" & lr)
         
        For Each rcell In rng.Cells
            If rcell.Value = "RAIL" Then
                If rcell.Offset(0, 1).Value = "FRANCE" Or rcell.Offset(0, 1).Value = "GERMANY" Then
                    If rcell.Offset(0, 13).Value >= 77 Then
                        rcell.Offset(0, 13).Interior.ColorIndex = 3
                    End If
                End If
            End If
             
        Next rcell

    Tuesday, June 13, 2017 10:45 PM

Answers

  • The default compare option in a module is binary, so maybe you have a spelling issue? Set the Compare Text option.

    And a code is better readable for humans if you write your code "as you speak"... instead of using Offset with numbers.

    Andreas.

    Option Compare Text
    
    Sub Test()
      Dim B As Range
      For Each B In Range("B1", Range("B" & Rows.Count).End(xlUp))
        With Range("O" & B.Row)
          'No color by default
          .Interior.ColorIndex = xlColorIndexNone
         
          If B.Value = "RAIL" Then
            Select Case Range("C" & B.Row)
              Case "FRANCE", "GERMANY"
                If .Value >= 77 Then .Interior.ColorIndex = 3
            End Select
          End If
        End With
      Next
    End Sub


    • Edited by Andreas Killer Wednesday, June 14, 2017 10:01 AM typo
    • Marked as answer by IamJackie Wednesday, June 14, 2017 8:55 PM
    Wednesday, June 14, 2017 10:01 AM

All replies

  • The default compare option in a module is binary, so maybe you have a spelling issue? Set the Compare Text option.

    And a code is better readable for humans if you write your code "as you speak"... instead of using Offset with numbers.

    Andreas.

    Option Compare Text
    
    Sub Test()
      Dim B As Range
      For Each B In Range("B1", Range("B" & Rows.Count).End(xlUp))
        With Range("O" & B.Row)
          'No color by default
          .Interior.ColorIndex = xlColorIndexNone
         
          If B.Value = "RAIL" Then
            Select Case Range("C" & B.Row)
              Case "FRANCE", "GERMANY"
                If .Value >= 77 Then .Interior.ColorIndex = 3
            End Select
          End If
        End With
      Next
    End Sub


    • Edited by Andreas Killer Wednesday, June 14, 2017 10:01 AM typo
    • Marked as answer by IamJackie Wednesday, June 14, 2017 8:55 PM
    Wednesday, June 14, 2017 10:01 AM
  • Thank you so very much! It worked! Made my day!!! :D 
    Wednesday, June 14, 2017 4:41 PM
  • Hello again Andreas, not sure if its okay to make a follow up question here. So I was trying to replicate the block of code above into another condition and making a few modifications.

    On my second condition, it says If it shows RAIL on Col B, either France or Germany on Col C, and less than 77 on Col O, color it red. I linked  the code below to the code above and it only did the job for the first condition. But when I removed the previous code above, my new code below is coloring applicable cells. Can you please tell me what is wrong with the code below? 

    Dim AB As Range
    For Each AB In Range("B1", Range("B" & Rows.count).End(xlUp))
        With Range("O" & AB.row)
          'No color by default
          .Interior.ColorIndex = xlColorIndexNone
         
          If AB.Value = "ALL-WATER" Then
            Select Case Range("C" & AB.row)
              Case "FRANCE", "GERMANY"
                If .Value < 77 Then .Interior.ColorIndex = 3
            End Select
          End If
        End With
      Next

    Thank you so much!

    Wednesday, June 14, 2017 8:40 PM
  • Can you please tell me what is wrong with the code below? 

          'No color by default
          .Interior.ColorIndex = xlColorIndexNone

    Why remove any color again? ;-)

    Andreas.

    Thursday, June 15, 2017 7:14 AM
  • OMG!!!!! It worked again! You're an angel!!! Thank you soooooo much! (jumping for joy!!!) :D:D:D
    Thursday, June 15, 2017 2:37 PM