VBA CODE FOR CONDITIONAL FORMULA

[vicky]

hey i need a vba code for conditional formula .... i want to check
this 3 conditions for every cell through vba code ...

= 120 then red colour

<50 then orange
inbetween 50 and 120 then yellow colour

 

[joel]

This code should work. You have to limit the range otherwise to g
through the entire worksheet takes a long time. There are more than on
shade of the colors and I picked the Standard shade for each of th
colors.

Const RedColor = 3
Const YellowColor = 6
Const OrangeColor = 46

Set DataRange = Range("A1500")

For Each cell In DataRange
If cell <> "" And IsNumeric(cell) Then

Select Case cell
Case Is < 50
cell.Interior.ColorIndex = OrangeColor
Case Is < 120
cell.Interior.ColorIndex = YellowColor
Case Else
cell.Interior.ColorIndex = RedColor
End Select
End If
Next cel

 

[OssieMac]

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.

 

[OssieMac]

Hi again Vicky,

Not sure if you need it but the code can be enhanced to only include numeric
cells and exclude blank cells as below.

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
If c.Value <> "" And _
IsNumeric(c.Value) Then

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
End If
Next c
End With

End Sub

 

[vicky]

thanks a lot !!!!!