Highlight Cells Based on Comparison Operator and Value (Max of 3 Conditions)
Use FormatConditions.Add FormatConditions is a property of the Range object. Add has the following parameters. The FormatConditions collection can only hold 3 FormatConditions.
FormatConditions.Add(Type, Operator, Formula1, Formula2)
The Type parameter has the following options. For this type of condition use xlCellValue.
|
Description |
Name |
Value |
|
Above average condition |
xlAboveAverageCondition |
12 |
|
Blanks condition |
xlBlanksCondition |
10 |
|
Cell value |
xlCellValue |
1 |
|
Color scale |
xlColorScale |
3 |
|
Databar |
xlDatabar |
4 |
|
Errors condition |
xlErrorsCondition |
16 |
|
Expression |
xlExpression |
2 |
|
Icon set |
XlIconSet |
6 |
|
No blanks condition |
xlNoBlanksCondition |
13 |
|
No errors condition |
xlNoErrorsCondition |
17 |
|
Text string |
xlTextString |
9 |
|
Time period |
xlTimePeriod |
11 |
|
Top 10 values |
xlTop10 |
5 |
|
Unique values |
xlUniqueValues |
8 |
The Operator parameter has the following options: xlBetween,xlEqual, xlGreater, xlGreaterEqual, xlLess, xlLessEqual, xlNotBetween, or xlNotEqual. Use this parameter in conjunction with the Formula 1 and Formula2 parameters.
Sub threecf()
Dim rg As Range
Dim cond1 As FormatCondition, cond2 As FormatCondition, cond3 As FormatCondition
Set rg = Range("A2", Range("A2").End(xlDown))
'clear any existing conditional formatting
rg.FormatConditions.Delete
'define the rule for each conditional format
Set cond1 = rg.FormatConditions.Add(xlCellValue, xlGreater, "=$a$1")
Set cond2 = rg.FormatConditions.Add(xlCellValue, xlLess, "=$a$1")
Set cond3 = rg.FormatConditions.Add(xlCellValue, xlEqual, "=$a$1")
'define the format applied for each conditional format
With cond1
.Interior.Color = vbGreen
.Font.Color = vbWhite
End With
With cond2
.Interior.Color = vbRed
.Font.Color = vbWhite
End With
With cond3
.Interior.Color = vbYellow
.Font.Color = vbRed
End With
End Sub
The code above applies the conditional formatting displayed below.
Highlight Cells Based on Comparison Operator and Value (No Limit on Rules)
To apply more than 3 conditional formats to a range you will need to use IF or Select Case – see code below.
Sub fourcf()
Dim rg As Range
Set rg = Range("A2", Range("A2").End(xlDown))
Dim target As Range
Set target = Range("A1")
Dim i As Long
Dim c As Long
Dim testcell As Range
c = rg.Cells.Count
For i = 1 To c
Set testcell = rg(i)
Select Case testcell
Case Is > target
With testcell
.Interior.Color = vbGreen
.Font.Color = vbWhite
End With
Case 0 To target - 1
With testcell
.Interior.Color = vbRed
.Font.Color = vbWhite
End With
Case Is = target
With testcell
.Interior.Color = vbYellow
.Font.Color = vbRed
End With
Case Is < 0
With testcell
.Value = "You cannot enter a negative value"
.Interior.Color = vbBlack
.Font.Color = vbWhite
End With
End Select
Next i
End Sub
The code above relates to the conditional formats applied to the data shown below.
