PDA

View Full Version : Solved: Change Cell Colors VBA



khalid79m
03-19-2010, 10:31 AM
I need a script to check all cells from : "a2:a" & xrow and if the value in the cell is between 0 - 200 , then colour the cell green if between 201 - 300 amber , if between 301 - 400 then red , and if above 401 pink.

Can anyone assist with the writing of this code ?? xld where are u ??

xld
03-19-2010, 11:09 AM
Sub ProcessData()
Dim LastRow As Long
Dim i As Long

With ActiveSheet

LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = 2 To LastRow

With .Cells(i, "A")

If IsNumeric(.Value2) Then

Select Case .Value2

Case Is <= 200: .Interior.ColorIndex = 10
Case Is <= 300: .Interior.ColorIndex = 45
Case Is < 400: .Interior.ColorIndex = 3
Case Else: .Interior.ColorIndex = 7
End Select
End If
End With
Next i
End With
End Sub

lucas
03-19-2010, 11:25 AM
I was running behind but I'll post it anyway.
Private Sub worksheet_change(ByVal target As Range)
If target.Count > 1 Then Exit Sub
If target.Column = 1 And target.Row <> 1 Then
Select Case target.Value
Case 1 To 200: target.Interior.ColorIndex = 4
Case 201 To 300: target.Interior.ColorIndex = 45
Case 301 To 400: target.Interior.ColorIndex = 3
Case Is > 400: target.Interior.ColorIndex = 7
Case Else: target.Interior.ColorIndex = xlAutomatic
End Select
End If
End Sub

khalid79m
03-25-2010, 10:32 AM
thanks guys, works a treat .. i used xld's code