» Conditonal Formatting using Microsoft Excel VBA

CATEGORY - Formating in VBA VERSION - All Microsoft Excel Versions

The tip submitted by Makati, Philippines

Private Sub cmdResult_Click()
     Dim atnt As Double
     Dim ave As Double
     Dim min As Double
     Dim max As Double

     For i = 6 To 9
      If IsNumeric(Worksheets("sheet1").Cells(i, 2)) And IsNumeric(Worksheets("sheet1").Cells(i, 3))_
      And IsNumeric(Worksheets("sheet1").Cells(i, 4)) And IsNumeric(Worksheets("sheet1").Cells(i, 5))_
      Then
      If Trim(Worksheets("sheet1").Cells(i, 2)) <> "" And Trim(Worksheets("sheet1").Cells(i, 3)) <> "" And_
      Trim(Worksheets("sheet1").Cells(i, 4)) <> "" And Trim(Worksheets("sheet1").Cells(i, 5)) <> "" Then
                 atnt = Worksheets("sheet1").Cells(i, 2)
                 ave = Worksheets("sheet1").Cells(i, 3)
                 min = Worksheets("sheet1").Cells(i, 4)
                 max = Worksheets("sheet1").Cells(i, 5)

                 If (atnt > ave) And (atnt > min) And (atnt > max) Then
                     Range("F" & i).Select
                     With Selection.Interior
                         .ColorIndex = 3
                         .Pattern = xlSolid
                     End With
                 ElseIf (atnt < ave) And (atnt < min) And (atnt < max) Then
                     Range("F" & i).Select
                     With Selection.Interior
                         .ColorIndex = 4
                         .Pattern = xlSolid
                     End With
                 ElseIf (atnt > ave) And (atnt < max) Then
                     Range("F" & i).Select
                     With Selection.Interior
                         .ColorIndex = 6
                         .Pattern = xlSolid
                     End With
                 End If
             End If
         End If
     Next i
End Sub