Conditional Formatting with 3 or more conditions

[sg2209]

Dear Friends,

Could you please guide me with the below conditional formatting , i need 3 conditions

Below 78% Red with White Fonts
Above 78% and Below 83% Yellow with Black Fonts
Above 83% Green with Black Fonts , how can i get the 2 condition, please help

Set rg = Range("R7", Range("R7").End(xlDown))
'clear any existing conditional formatting
rg.FormatConditions.Delete
Set cond1 = rg.FormatConditions.Add(xlCellValue, xlLess, "=78%")
With cond1
.Interior.Color = vbRed
.Font.Color = vbWhite
End With


Set cond2 = rg.FormatConditions.Add(xlCellValue, xlGreater, "=83%")
With cond2
.Interior.Color = vbGreen
.Font.Color = vbBlack
End With


Set cond3 = rg.FormatConditions.Add(xlCellValue, xlLess, "=83%")
With cond3
.Interior.Color = vbGreen
.Font.Color = vbBlack
End With

 

[rlv01]

    Set cond1 = rg.FormatConditions.Add(xlCellValue, xlGreater, "=83%")
    With cond1
        .Interior.Color = vbGreen
        .Font.Color = vbBlack
    End With

    Set cond2 = rg.FormatConditions.Add(xlCellValue, xlLess, "=78%")
    With cond2
       .Interior.Color = vbRed
        .Font.Color = vbWhite
    End With

    Set cond3 = rg.FormatConditions.Add(xlCellValue, xlLess, "=83%")
    With cond3
        .Interior.Color = vbYellow
        .Font.Color = vbBlack
    End With

 

[ravishankar]

run the macro below to decide which font / interior combination best suits you.
Sub spectrum()

Dim a As Integer, b As Integer
    For a = 2 To 56
        For b = 2 To 56
        Cells(a, 1) = a
        Cells(1, b) = b
        Cells(a, b) = "SG2209"
        Cells(a, b).Font.ColorIndex = a
        Cells(a, b).Interior.ColorIndex = b
        Next b
    Next a
MsgBox " complete"
End Sub

use those color index in the following macro

Sub sg2219()
Dim LR As Long, a As Long
 LR = Cells(Rows.Count, "R").End(xlUp).Row
 MsgBox LR
    For a = 7 To LR
       If Cells(a, "R") > 83 Then
       Cells(a, "R").Font.ColorIndex = 3
       Cells(a, "R").Interior.ColorIndex = 10
       ElseIf Cells(a, "R") >= 78 And Cells(a, "R") <= 83 Then
       Cells(a, "R").Font.ColorIndex = 4
       Cells(a, "R").Interior.ColorIndex = 11
        ElseIf Cells(a, "R") < 78 Then
       Cells(a, "R").Font.ColorIndex = 5
       Cells(a, "R").Interior.ColorIndex = 12
       End If
    Next a
    MsgBox " complete"
 End Sub

ravishankar