How to make a Case Statement Loop through Column

[Tash_081]

Hi All, newbie to VBA here so may be dumb question but I am stuck. I have this Case Statement that works as intended however I cant figure out how to make it loop through from K3 through the rest of the column until last row. Any help would be much appreciated.

Sub ShiftColour()

Select Case Range("K3").Value

Case "A", "E", "N", "L", "AP", "EP", "NP", "LP", "AI", "EI", "NI", "LI"
Range("J3").Value = "ES BLACK"
Case "B", "BP", "BI"
Range("J3").Value = "BROWNB"
Case "C", "CP", "CI"
Range("J3").Value = "DARKGREEN"
Case "D", "DP", "DI"
Range("J3").Value = "PURPLED"

End Select

End Sub

 

[rlv01]

In general, it would be something like this.

Sub ShiftColour()
    Dim rng As Range
    Dim R As Range
    
    With ActiveSheet
        Set rng = .Range("K3", .Range("K" & .Rows.Count).End(xlUp))
    End With
    
    For Each R In rng
        Select Case R.Value
            Case "A", "E", "N", "L", "AP", "EP", "NP", "LP", "AI", "EI", "NI", "LI"
                R.Offset(0, -1).Value = "ES BLACK"
            Case "B", "BP", "BI"
                R.Offset(0, -1).Value = "BROWNB"
            Case "C", "CP", "CI"
                R.Offset(0, -1).Value = "DARKGREEN"
            Case "D", "DP", "DI"
                R.Offset(0, -1).Value = "PURPLED"
        End Select
    Next R
End Sub

(Tip: For future posts , please try to use code tags like I did above when posting code. It makes your code easier to read and copy.

How to Post Your VBA Code

Post your VBA code.

www.mrexcel.com

)

 

[mehidy1437]

Try it

Sub ShiftColour()

Dim lastRow As Long
lastRow = Cells(Rows.Count, "K").End(xlUp).Row

For i = 3 To lastRow
    Select Case Range("K" & i).Value
        Case "A", "E", "N", "L", "AP", "EP", "NP", "LP", "AI", "EI", "NI", "LI"
            Range("J" & i).Value = "ES BLACK"
        Case "B", "BP", "BI"
            Range("J" & i).Value = "BROWNB"
        Case "C", "CP", "CI"
            Range("J" & i).Value = "DARKGREEN"
        Case "D", "DP", "DI"
            Range("J" & i).Value = "PURPLED"
    End Select
Next i

End Sub

 

[Tash_081]

Both options worked. Thank you so much!