Simplify Condition Code

[xsitm]

I've created a code to color rows based on a condition. However I can't seem to get the coding right to only look at a row if there is data in Column I. Right now it checks every row up until 20,000.

Any help simplifying would be greatly appreciated.


Sub Color()
Dim LRow As Integer<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
Dim LCell As String<o></o>
Dim LColorCells As String<o></o>
'Start at row 2<o></o>
LRow = 2<o></o>
'Update row colors for the first 20000 rows<o></o>
While LRow < 20000<o></o>
LCell = "I" & LRow<o></o>
'Color will changed in columns A to U<o></o>
LColorCells = "A" & LRow & ":" & "U" & LRow<o></o>
Select Case Left(Range(LCell).Value, 6)<o></o>
'Set row color to light purple<o></o>
Case "A"<o></o>
Range(LColorCells).Interior.ColorIndex = 7<o></o>
Range(LColorCells).Interior.Pattern = xlSolid<o></o>
'Set row color to light yellow<o></o>
Case "AM"<o></o>
Rows(LRow & ":" & LRow).Select<o></o>
Range(LColorCells).Interior.ColorIndex = 44<o></o>
Range(LColorCells).Interior.Pattern = xlSolid<o></o>
End Select<o></o>
LRow = LRow + 1<o></o>
Wend<o></o>
Range("A1").Select<o></o>

End Sub

 

[venkat1926]

what the conditions for changing color
It is difficult to find the conditions form the macro

e.g.

Select Case Left(Range(LCell).Value, 6)

what does this mean ???
what condition do you want to set up.

 

[AlphaFrog]

I agree with venkat1926

Here's your code cleaned up a bit.

Sub Color()

    Dim LastRow As Long
    Dim cell As Range

    LastRow = Range("I" & Rows.Count).End(xlUp).Row 'Last used row in column I
    If LastRow > 20000 Then LastRow = 20000
    
    'Update row colors for the first 20000 rows
    For Each cell In Range("I2:I" & LastRow)
        'Color will changed in columns A to U
        With Range("A" & cell.Row).Resize(, 21).Interior 'Columns A to U
            Select Case Left(cell.Value, 6)
                Case "A": .ColorIndex = 7   'light purple
                Case "AM": .ColorIndex = 44 'light yellow
                Case Else: .ColorIndex = xlNone
            End Select
            .Pattern = xlSolid
        End With
    Next cell

End Sub