Hi Vicky,
Try the following code. However, consider Conditional format for which I
have supplied instructions below the code.
Sub ConditFormat()
'>= 120 then red colour
'<50 then orange
'inbetween 50 and 120 then yellow
Dim c As Range
'Edit "Sheet1" to your sheet name
With Sheets("Sheet1")
For Each c In .UsedRange
Select Case c.Value
Case Is >= 120
c.Interior.ColorIndex = 3
Case Is < 50
c.Interior.ColorIndex = 46
Case 50 To 120
c.Interior.ColorIndex = 6
End Select
Next c
End With
End Sub
Conditional Format. Following instructions for how.
NOTE: In the formulas below for both xl2007 and earlier versions, change A1
to the first cell of your selection. (XL looks after setting the remaining
cells of the selection)
Instructions for xl2007
Select the range to have the formatting.
Select Conditional formatting (Styles block in Home ribbon)
Select Manage Rules
Select New rule
Select Use formula to determine which cells to format
Enter the formula =A1>=120
Click Format button and set required format (Red)
Click OK and OK again
Select New rule
Select Use formula to determine which cells to format
Enter the formula =A1<50
Click Format button and set required format (Orange)
Click OK and OK again
Select New rule
Select Use formula to determine which cells to format
Enter the formula =AND(A1>=50,A1<120)
Click Format button and set required format (Orange)
Click OK and OK again
End of xl2007 instructions
***********************************************
Instructions for earlier versions of xl.
Select the range to have the formatting.
Select menu item Format -> Conditional Format
Under Condition1, Select formula is
Enter the formula =A1>=120
Click Format button and set required format (Red)
Click OK
Click Add
Under Condition2, Select formula is
Enter the formula =A1<50
Click Format button and set required format (Orange)
Click OK
Click Add
Under Condition3, Select formula is
Enter the formula =AND(A1>=50,A1<120)
Click Format button and set required format (Yellow)
Click OK
Click OK to finish.
