Solved: Colour change based on cell value

[khalid79m]

    For Each a In Array("M", "P", "U", "X", "AC", "AF", "AK", "AN", "AS", "AV", "AZ")
    With ThisWorkbook.Sheets("Agent_View")
        For i = 18 To 47
             With .Cells(i, a)
                If IsNumeric(.Value2) Then
                    Select Case .Value2
                    Case Is <= 200: .Interior.ColorIndex = 43
                    Case Is <= 250: .Interior.ColorIndex = 44
                    Case Is >= 251: .Interior.ColorIndex = 3
                    End Select
                Else
                    Cells(i, a).Interior.ColorIndex = 0
                End If
            End With
        Next i
    End With
    Next a

I have this code but it doesnt work the way I need it to , can anyone assit, if column az18 is
<=200 then a18:h18 = .Interior.ColorIndex = 43
<= 250 then a18:h18 = .Interior.ColorIndex = 44
>= 251: then a18:h18 = .Interior.ColorIndex = 3

then do the same with az19 all the way to az47

can anyone help ?

[Bob Phillips]

[vba]

With ThisWorkbook.Sheets("Agent_View")
For i = 18 To 47
With .Cells(i, "AZ")
If IsNumeric(.Value2) Then
Select Case .Value2
Case Is <= 200: .Cells(i, "A").Resize(, 8).Interior.ColorIndex = 43
Case Is <= 250: .Cells(i, "A").Resize(, 8).Interior.ColorIndex = 44
Case Is >= 251: .Cells(i, "A").Resize(, 8).Interior.ColorIndex = 3
End Select
Else
Cells(i, "A").Resize(, 8).Interior.ColorIndex = xlColorIndexNone
End If
End With
Next i
End With
[/vba]

[khalid79m]

this doesnt work as it resize elswhere on the screen

[Bob Phillips]

Maybe

[vba]

With ThisWorkbook.Sheets("Agent_View")
For i = 18 To 47
With .Cells(i, "AZ")
If IsNumeric(.Value2) Then
Select Case .Value2
Case Is <= 200: .offset(), -51).Resize(, 8).Interior.ColorIndex = 43
Case Is <= 250: .offset(), -51).Resize(, 8).Interior.ColorIndex = 44
Case Is >= 251: .offset(), -51).Resize(, 8).Interior.ColorIndex = 3
End Select
Else
Cells(i, "A").Resize(, 8).Interior.ColorIndex = xlColorIndexNone
End If
End With
Next i
End With
[/vba]

[khalid79m]

HI xld both codes dont work, the first code starts colouring from row az35 downwards , the second code just throws up errors

[Bob Phillips]

Some extra brackets crept in there

[vba]

With ThisWorkbook.Sheets("Agent_View")
For i = 18 To 47
With .Cells(i, "AZ")
If IsNumeric(.Value2) Then
Select Case .Value2
Case Is <= 200: .Offset(, -51).Resize(, 8).Interior.ColorIndex = 43
Case Is <= 250: .Offset(, -51).Resize(, 8).Interior.ColorIndex = 44
Case Is >= 251: .Offset(, -51).Resize(, 8).Interior.ColorIndex = 3
End Select
Else
Cells(i, "A").Resize(, 8).Interior.ColorIndex = xlColorIndexNone
End If
End With
Next i
End With
[/vba]

[khalid79m]

thanks , worked