VBA Code
Color and ColorIndex
The Color and ColorIndex properties of a Range object do not return the colour of the cell if the formatting has been applied as a consequence of Conditional Formatting.
You can however use the DisplayFormat property to access the conditional formatting format.
Note that the DisplayFormat property will not work in a User Defined Function.
Sub WhichTypeOfFormatting()
Dim sCellAddress As String
sCellAddress = "B1"
If (Range(sCellAddress).Interior.ColorIndex <> XlColorIndex.xlColorIndexNone) Then
Debug.Print sCellAddress & " has cell formatting"
Else
If (Range(sCellAddress).DisplayFormat.Interior.ColorIndex <> XlColorIndex.xlColorIndexNone) Then
Debug.Print sCellAddress & " has conditional formatting"
Else
Debug.Print sCellAddress & " has no formatting"
End If
End If
End Sub
Using the Range.FormatConditions Collection
This collection contains all the conditional formats for a single range.
The FormatConditions collection can contain up to three conditional formats.
Each format is represented by a FormatCondition object.
Use the FormatConditions property to return a FormatConditions object.
Use the Add method to create a new conditional format, and use the Modify method to change an existing conditional format.
With Worksheets(1).Range("A1:A10").FormatConditions _
.Add(xlCellValue, xlGreater, "=$A$1")
With .Borders
.LineStyle = xlLineStyle.xlContinuous
.Weight = xlBorderWeight.xlThin
.ColorIndex = 6
End With
With .Font
.Bold = True
.ColorIndex = 3
End With
End With
If you try to create more than three conditional formats for a single range, the Add method fails.
If a range has three formats, you can use the Modify method to change one of the formats, or you can use the Delete method to delete a format and then use the Add method to create a new format.
FormatCondition Object
Represents a conditional format. The FormatCondition object is a member of the FormatConditions collection.
Use FormatConditions(index), where index is the index number of the conditional format, to return a FormatCondition object.
With Worksheets(1).Range("A1:A10").FormatConditions(1)
With .Borders
.LineStyle = xlLineStyle.xlContinuous
.Weight = xlBorderWeight.xlThin
.ColorIndex = 6
End With
With .Font
.Bold = True
.ColorIndex = 3
End With
End With
Use the Font, Border, and Interior properties of the FormatCondition object to control the appearance of formatted cells. Some properties of these objects aren't supported by the conditional format object model. The properties that can be used with conditional formatting are listed in the following table.
Font Object
Bold
Color
ColorIndex
FontStyle
Italic
Strikethrough
Underline
The accounting underline styles cannot be used.
Border Object
Bottom
Color
Left
Right
Style
The following border styles can be used (all others aren't supported): xlNone, xlSolid, xlDash, xlDot, xlDashDot, xlDashDotDot, xlGray50, xlGray75, and xlGray25.
Top
Weight
The following border weights can be used (all others aren't supported): xlWeightHairline and xlWeightThin.
Interior Object
Color
ColorIndex
Pattern
PatternColorIndex
Formula1 Property
Copying and pasting a cell over a cell that uses conditional formatting wipes out the formatting rules
There is no direct way for your VBA code to determine if a particular cell's conditional formatting has been "triggered."
When a Conditional Formatting formula uses a relative range reference, accessing the Formula1 using VBA gives you a different formula, depending on the active cell position.
Convert the formula to R1C1 notation using the active cells as the reference
Then convert that R1C1 formula back to A1 style
F1 = Range("A1").FormatConditions(1).Formula1
F2 = Application.ConvertFormula(F1, xlA1, xlR1C1, , ActiveCell)
F1 = Application.ConvertFormula(F2, xlR1C1, xlA1, , Range("A1"))
Highlighting the first "different" value in a column
Public Function FirstDifferentValue(ByVal rgeRange As Range) As Boolean
Static bpreviousshaded As Boolean
If rgeRange.Row = 1 Then
bpreviousshaded = True
FirstDifferentValue = True
Exit Function
End If
If rgeRange.Value = rgeRange.Offset(-1, 0).Value Then
bpreviousshaded = True
FirstDifferentValue = True
Else
FirstDifferentValue = Not bpreviousshaded
bpreviousshaded = False
End If
End Function
Highlighting alternate values in a column
Public Function DifferentDate(ByVal rgeRange As Range) As Boolean
Static bpreviousshaded As Boolean
If rgeRange.Row = 1 Then
bpreviousshaded = False
DifferentDate = False
Exit Function
End If
If rgeRange.Value = rgeRange.Offset(-1, 0).Value Then
DifferentDate = bpreviousshaded
Else
If bpreviousshaded = False Then
DifferentDate = True
bpreviousshaded = True
Else
DifferentDate = False
bpreviousshaded = False
End If
End If
End Function
Highlighting the other columns in a table
Public Function ShadeColumn(ByVal rgeRange As Range, _
ByVal iValueCol As Integer) As Boolean
Static bpreviousshaded(10) As Boolean
If rgeRange.Row = 1 Then
bpreviousshaded(rgeRange.Column) = False
ShadeColumn = False
Exit Function
End If
If rgeRange.Offset(0, iValueCol - rgeRange.Column).Value = _
rgeRange.Offset(-1, iValueCol - rgeRange.Column).Value Then
ShadeColumn = bpreviousshaded(rgeRange.Column)
Else
If bpreviousshaded(rgeRange.Column) = False Then
ShadeColumn = True
bpreviousshaded(rgeRange.Column) = True
Else
ShadeColumn = False
bpreviousshaded(rgeRange.Column) = False
End If
End If
End Function
Adding
This will add conditional formatting to cells "A2:C8" indicating the smallest number in each row, excluding zeros.
Sub ApplyConditionalFormatting()
Dim oFormatCondition As FormatCondition
On Error Resume Next
Set oFormatCondition = Range("A2:C8").FormatConditions(1)
oFormatCondition.Delete
On Error GoTo 0
Range("A2:C8").Select
Set oFormatCondition = Range("A2:C8").FormatConditions.Add( _
XlFormatConditionType.xlExpression, , _
"=A2=SMALL($A2:$C2,COUNTIF($A2:$C2,0)+1)")
With oFormatCondition
.Interior.Color = RGB(212, 152, 112)
End With
End Sub
Finding
This will display a list of all the ranges in the active workbook that contain conditional formatting
Sub ListAllConditionalFormats()
Dim oWsh As Worksheet
Dim oFormatCondition As FormatCondition
Dim lwshcount As Long
Dim lcount As Long
For lwshcount = 1 To ActiveWorkbook.Sheets.Count
Set oWsh = Sheets(lwshcount)
For lcount = 1 To oWsh.Cells.FormatConditions.Count
Set oFormatCondition = oWsh.Cells.FormatConditions(lcount)
Debug.Print oWsh.Name & " - " & oFormatCondition.AppliesTo.Address
Next lcount
Next lwshcount
End Sub
Copying
Sub CopyConditionalFormats(
Dim m_FormatConditions As Collection
Dim oWsh As Worksheet
Dim oFormatCondition As FormatCondition
Dim lwshcount As Long
Dim lcount As Long
Dim ltotalcount As Long
Dim scellrange As String
Dim swshname As String
Dim sformula1 As String
Dim enOperator As XlFormatConditionOperator
Dim enType As XlFormatConditionType
Dim returnCondition As FormatCondition
Set m_FormatConditions = New Collection
ltotalcount = 1
For lwshcount = 1 To ThisWorkbook.Sheets.Count
Set oWsh = ThisWorkbook.Sheets(lwshcount)
For lcount = 1 To oWsh.Cells.FormatConditions.Count
Set oFormatCondition = oWsh.Cells.FormatConditions(lcount)
Call m_FormatConditions.Add(Item:=oFormatCondition, Key:="Item-" & ltotalcount)
Debug.Print oWsh.Name & " - " & oFormatCondition.AppliesTo.Address
ltotalcount = ltotalcount + 1
Next lcount
Next lwshcount
For lcount = 1 To m_FormatConditions.Count
Set oFormatCondition = m_FormatConditions(lcount)
swshname = oFormatCondition.AppliesTo.Worksheet.Name
Set oWsh = ThisWorkbook.Sheets(swshname)
scellrange = oFormatCondition.AppliesTo.Cells.Address
enType = oFormatCondition.Type
enOperator = oFormatCondition.Operator
sformula1 = oFormatCondition.Formula1
returnCondition = oWsh.Range(scellrange).FormatConditions.Add( _
Type:=enType, _
Operator:=enOperator, _
Formula1:=sformula1)
Next lcount
End Sub
Removing
This will remove the conditional formatting from rows 2 to 10 that contain the text "Remove" in column "H".
Sub RemoveConditionalFormatting()
Dim lrowno As Long
For lrowno = 2 To 10
If (Range("H" & lrowno) = "Remove") Then
Range("A" & lrowno & ":G" & lrowno).ClearFormats
End If
Next lrowno
End Sub
Using the Worksheet OnEntry property
This method is an alternative to using the Conditional Formatting feature.
The OnEntry event is fired whenever the user enters data in that particular worksheet.
You can use the OnEntry property of either a worksheet or application object.
This event will fire after the user has entered (or modified) the contents of a cell by pressing Enter or by selecting another cell with the mouse.
This event will not fire if the user uses (Edit > Cut) or (Edit > Paste) or if another subroutine changes the contents of any of the cells.
Private Sub Workbook_Open()
Sheets("sheetname").OnEntry = "ConditionalFormatting"
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Sheets("sheetname").OnEntry = ""
End Sub
Any cells on this particular worksheet that have a value greater than 20 will be automatically displayed in bold.
Public Sub ConditionalFormatting()
If IsNumeric(ActiveCell.Value) = True Then
If ActiveCell.Value > 20 Then
ActiveCell.Font.Bold = True
Else
ActiveCell.Font.Bold = False
End If
End If
End Sub
Using the Worksheet Change event
In Excel 97 this was replaced with the worksheet Change event and the application SheetChange event.
This subroutine has to appear in the corresponding worksheet code module.
Private Sub Worksheet_Change(ByVal Target As Range)
If IsNumeric(Target.Value) = True Then
If Target.Value > 20 Then
Target.Font.Bold = True
Else
Target.Font.Bold = False
End If
End If
End Sub
© 2026 Better Solutions Limited. All Rights Reserved. © 2026 Better Solutions Limited TopPrev