PDA

View Full Version : Solved: Colour change based on cell value



khalid79m
06-17-2010, 09:25 AM
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
06-17-2010, 09:37 AM
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

khalid79m
06-17-2010, 10:47 AM
this doesnt work as it resize elswhere on the screen

Bob Phillips
06-17-2010, 12:02 PM
Maybe



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

khalid79m
06-18-2010, 06:33 AM
HI xld both codes dont work, the first code starts colouring from row az35 downwards , the second code just throws up errors

Bob Phillips
06-18-2010, 07:04 AM
Some extra brackets crept in there



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

khalid79m
06-30-2010, 02:34 PM
thanks , worked