VBA Help!

[steveo0707]

The following is the code I have written for my worksheet. I am getting the program to do everything except for the last Case Parameters. It's not putting the © in the F column nor shading the cell green. I have also already tried making it an If statement and that did not work either.

Sub Scanning()
    Dim YELLOW As Variant
    Dim OFF_GREEN As Variant
    Dim GREEN As Variant
    Dim Row As Long
    ' DEFINE THE COLORS
    YELLOW = RGB(255, 255, 91)
    OFF_GREEN = RGB(196, 215, 155)
    GREEN = RGB(54, 248, 54)
    ' CYCLE THROUGH ROWS
    Row = 1 
          
    Do Until Range("E" & Row).Value = VBA.Constants.vbNullString
        ' CHECKING COLUMN E
        Select Case Range("E" & Row).Value
            Case "Working On"
                Range("A" & Row).Interior.Color = YELLOW
            Case "In Box"
                Range("A" & Row & ":B" & Row).Interior.Color = YELLOW
            Case "Crate Built"
                Range("F" & Row).Value = "©"
                Range("F" & Row).Interior.Color = GREEN
                
         End Select
        
               ' IF YOU ARE LOOKING FOR JUST A LETTER D IN COLUMN F
        If Range("F" & Row).Value = "D" Then Range("C" & Row).Interior.Color = OFF_GREEN
               
        Row = Row + 1
    Loop
End Sub

 

[Fluff]

If you step through the code, how many times does it go through the loop?
Having had another look at your image, I suspect it's only going through once.

 

[steveo0707]

yes it is only going through once.

 

[Fluff]

Ok how about

Sub Scanning()
   Dim YELLOW As Variant
   Dim OFF_GREEN As Variant
   Dim GREEN As Variant
   Dim Cl As Range
   
   ' DEFINE THE COLORS
   YELLOW = RGB(255, 255, 91)
   OFF_GREEN = RGB(196, 215, 155)
   GREEN = RGB(54, 248, 54)
   ' CYCLE THROUGH ROWS
   
   For Each Cl In Range("E2", Range("E" & Rows.Count).End(xlUp))
   ' CHECKING COLUMN E
      Select Case Cl.Value
         Case "Working On"
            Cl.Offset(, -4).Interior.Color = YELLOW
         Case "In Box"
            Cl.Offset(, -4).Resize(, 2).Interior.Color = YELLOW
         Case "Crate Built"
            Cl.Offset(, 1).Value = "©"
            Cl.Offset(, 1).Interior.Color = GREEN
      End Select
      
      ' IF YOU ARE LOOKING FOR JUST A LETTER D IN COLUMN F
      If Cl.Offset(, 1).Value = "D" Then Cl.Offset(, -2).Interior.Color = OFF_GREEN
      
   Next Cl
End Sub

 

[steveo0707]

Works Perfectly! Thank You so much for the help!

 

[steveo0707]

One last question. Once I have shipped this item, I need cells to go back to having no highlights. How would I incorporate that into the code?

 

[Fluff]

Maybe

   For Each Cl In Range("E2", Range("E" & Rows.Count).End(xlUp))
   ' CHECKING COLUMN E
      Select Case Cl.Value
         Case "Working On"
            Cl.Offset(, -4).Interior.Color = YELLOW
         Case "In Box"
            Cl.Offset(, -4).Resize(, 2).Interior.Color = YELLOW
         Case "Crate Built"
            Cl.Offset(, 1).Value = "©"
            Cl.Offset(, 1).Interior.Color = GREEN
         Case Else
            Cl.Offset(, -4).Resize(, 2).Interior.Color = xlNone
            Cl.Offset(, 1).Interior.Color = xlNone
      End Select

 

[steveo0707]

Thank You again for all the help!

 

[Fluff]

You're welcome & thanks for the feedback