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]

Do you have any cells in col E with "Crate Built", bearing in mind that VBA is case sensitive?
Also check for any leading/trailing spaces.

 

[steveo0707]

Yes, I have several cells with "Crate Built" and there are no extra spaces.

 

[Fluff]

What happens if you step through the code using F8. When you get to a row with Crate Built, does it move to this line

Range("F" & Row).Value = "©"

 

[steveo0707]

When I use F8, it runs through the code and only highlights the case lines but not any of the Range lines.

 

[Fluff]

In that case the values in col E are no exactly the same as the case statements. Bearing in mind crate built, Crate built, crate Built etc are not the same as Crate Built.

 

[steveo0707]

I have attached a Screen shot of the work book

 

[Fluff]

Ok, in a blank cell put
=E4="Crate Built"
What does it return?

 

[Peter_SSs]

Sounds like your "space' characters are not standard ones. What happens if you change your Select Case statement to this?

Select Case Replace(Range("E" & Row).Value, Chr(160), " ")

 

[Fluff]

Also try
=CODE(MID(E4,6,1))
What does this return?