Conditional Formatting Rules Not Applying

Nuke_It_Newport

Nuke_It_Newport

New Member
Joined
Nov 17, 2020
Messages
47
Office Version
  1. 365
Platform
  1. Windows
Happy Sunday everyone-

I have the following code that sets conditional formatting rules for the currently selected row and cell. When the user selects a different row or cell, I need to delete the CF rules and apply a new set of rules.

VBA Code: 
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)

Dim listObj As ListObject
Dim prevRange As Range
Dim ws As Worksheet

'Check if target is inside table
If Intersect(ActiveCell, ActiveSheet.ListObjects(1).DataBodyRange) Is Nothing Then Exit Sub

If prevRange Is Nothing Then
    Set prevRange = Target
    Exit Sub
End If

Set ws = ActiveSheet
Set listObj = ws.ListObjects(1)

'$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$
'Format Active Cell
'$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$
With Target.FormatConditions.Add(Type:=xlExpression, Formula1:="=TRUE")
    .StopIfTrue = False
    With .Interior
        .Color = clrLtGray
        .TintAndShade = 0
    End With
End With

'$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$
'Format Active Row
'$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$
With Intersect(ActiveCell.EntireRow, listObj.DataBodyRange).FormatConditions.Add(Type:=xlExpression, Formula1:="=TRUE")
    .StopIfTrue = False
    With .Interior
        .Color = clrLtYellow
        .TintAndShade = 0
    End With
End With

'$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$
'Set font color for previously selected row (entire table row) based on value in Col "T" of same row
''$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$
With Intersect(prevRange.EntireRow, listObj.DataBodyRange)
    .FormatConditions.Delete
    .FormatConditions.Add Type:=xlExpression, Formula1:="=$T5=""Gray"""
    With .FormatConditions(1)
        .StopIfTrue = False
        With .Font
            .Bold = False
            .Italic = False
            .Strikethrough = True
            .Color = clrGray
            .TintAndShade = 0
        End With
    End With
End With
'$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$
'Set font color for previously selected row, column 1, based on color in column "T" of same row
'$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$
With Intersect(prevRange.EntireRow, listObj.ListColumns(1).DataBodyRange)
    .FormatConditions.Add Type:=xlExpression, Formula1:="=$T5=""Black"""
    With .FormatConditions(2)
    .StopIfTrue = False
        With .Font
            .Bold = False
            .Italic = False
            .Strikethrough = False
            .Color = clrBlack
            .TintAndShade = 0
        End With
    End With
    .FormatConditions.Add Type:=xlExpression, Formula1:="=$T5=""Red"""
    With .FormatConditions(3)
        .StopIfTrue = False
        With .Font
            .Bold = False
            .Italic = False
            .Strikethrough = False
            .Color = clrRed
            .TintAndShade = 0
        End With
    End With
.FormatConditions.Add Type:=xlExpression, Formula1:="=$T5=""Green"""
    With .FormatConditions(4)
        .StopIfTrue = False
        With .Font
            .Bold = False
            .Italic = False
            .Strikethrough = False
            .Color = clrGreen
            .TintAndShade = 0
        End With
    End With
.FormatConditions.Add Type:=xlExpression, Formula1:="=$T5=""Orange"""
    With .FormatConditions(5)
        .StopIfTrue = False
        With .Font
            .Bold = False
            .Italic = False
            .Strikethrough = False
            .Color = clrOrange
            .TintAndShade = 0
        End With
    End With
End With

'$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$
'Set font color for previously selected row, column 5, based on color in column "U" of same row
'$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$
With Intersect(prevRange.EntireRow, listObj.ListColumns(5).DataBodyRange)
    .FormatConditions.Add Type:=xlExpression, Formula1:="=$U5=""Black"""
    With .FormatConditions(2)
    .StopIfTrue = False
        With .Font
            .Bold = False
            .Italic = False
            .Strikethrough = False
            .Color = clrBlack
            .TintAndShade = 0
        End With
    End With
    .FormatConditions.Add Type:=xlExpression, Formula1:="=$U5=""Red"""
    With .FormatConditions(3)
        .StopIfTrue = False
        With .Font
            .Bold = False
            .Italic = False
            .Strikethrough = False
            .Color = clrRed
            .TintAndShade = 0
        End With
    End With
.FormatConditions.Add Type:=xlExpression, Formula1:="=$U5=""Green"""
    With .FormatConditions(4)
        .StopIfTrue = False
        With .Font
            .Bold = False
            .Italic = False
            .Strikethrough = False
            .Color = clrGreen
            .TintAndShade = 0
        End With
    End With
.FormatConditions.Add Type:=xlExpression, Formula1:="=$U5=""Orange"""
    With .FormatConditions(5)
        .StopIfTrue = False
        With .Font
            .Bold = False
            .Italic = False
            .Strikethrough = False
            .Color = clrOrange
            .TintAndShade = 0
        End With
    End With
End With
'$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$
'Set font color for previously selected row, column 11, based on color in column "V" of same row
'$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$
With Intersect(prevRange.EntireRow, listObj.ListColumns(11).DataBodyRange)
    .FormatConditions.Add Type:=xlExpression, Formula1:="=$V5=""Black"""
    With .FormatConditions(2)
    .StopIfTrue = False
        With .Font
            .Bold = False
            .Italic = False
            .Strikethrough = False
            .Color = clrBlack
            .TintAndShade = 0
        End With
    End With
    .FormatConditions.Add Type:=xlExpression, Formula1:="=$V5=""Red"""
    With .FormatConditions(3)
        .StopIfTrue = False
        With .Font
            .Bold = False
            .Italic = False
            .Strikethrough = False
            .Color = clrRed
            .TintAndShade = 0
        End With
    End With
.FormatConditions.Add Type:=xlExpression, Formula1:="=$V5=""Green"""
    With .FormatConditions(4)
        .StopIfTrue = False
        With .Font
            .Bold = False
            .Italic = False
            .Strikethrough = False
            .Color = clrGreen
            .TintAndShade = 0
        End With
    End With
.FormatConditions.Add Type:=xlExpression, Formula1:="=$V5=""Orange"""
    With .FormatConditions(5)
        .StopIfTrue = False
        With .Font
            .Bold = False
            .Italic = False
            .Strikethrough = False
            .Color = clrOrange
            .TintAndShade = 0
        End With
    End With
End With

Set prevRange = Target

End Sub

What is happening is the CF rules are applying correctly for the active row and cell, but when the user selects a different row or cell, the new rules are not applying correctly. The following CF rules are applied no matter what the value is in the helper column that the CF rule is to reference. In other words, the row that was previously selected gets formatted to a font color of gray with strikethrough per the following code. It should format according to the color in the helper column based on the subsequent code in the module, as see in the above code snippet.

Code: 
'$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$
'Set font color for previously selected row (entire table row) based on value in Col "T" of same row
''$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$
With Intersect(prevRange.EntireRow, listObj.DataBodyRange)
    .FormatConditions.Delete
    .FormatConditions.Add Type:=xlExpression, Formula1:="=$T5=""Gray"""
    With .FormatConditions(1)
        .StopIfTrue = False
        With .Font
            .Bold = False
            .Italic = False
            .Strikethrough = True
            .Color = clrGray
            .TintAndShade = 0
        End With
    End With
End With

Can anybody give me some guidance?

Thanks!
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
Sort by date Sort by votes
Good morning everyone-

If anyone is interested, I got everything working after much trial and error.

Code: 
Option Explicit

Public Const clrBlack = "0"  '   Black
Public Const clrGray = "8421504"    '   Gray
Public Const clrGreen = "32768"  ' Green
Public Const clrRed = "255"   '   Dark Red
Public Const clrOrange = "26367"   '   Orange
Public Const clrLtGray = 15790320   '   Light gray for active cell
Public Const clrLtYellow = 10092543     '   Light yellow for active row
Public rTarget As Range

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)

'Check if target is inside table
If Intersect(ActiveCell, ActiveSheet.ListObjects(1).DataBodyRange) Is Nothing Then Exit Sub

Set ws = ActiveSheet
Set listObj = ws.ListObjects(1)
Set rTarget = Target

SetActiveRowCFRules
SetPrevRowCFRules

'Set Active Cell Highlighting
With Target.FormatConditions.Add(Type:=xlExpression, Formula1:="=TRUE")
    With .Interior
        .Color = clrLtGray
    End With
End With

'Set Active Row Highlighting
With Target.EntireRow.FormatConditions.Add(Type:=xlExpression, Formula1:="=TRUE")
    With .Interior
        .Color = clrLtYellow
    End With
End With

Set prevRange = Target

End Sub

Code: 
Option Explicit

Sub SetActiveRowCFRules()

Set listObj = ws.ListObjects(1)

'Format Expired
With rTarget.EntireRow
    With .FormatConditions
        .Delete
        .Add Type:=xlExpression, Formula1:="=$T5=""Gray"""
    End With

    With .FormatConditions(1)
        .StopIfTrue = False
        With .Font
            .Bold = False
            .Italic = False
            .Strikethrough = True
            .Color = clrGray
            .TintAndShade = 0
        End With
    End With
End With

'Format Work Order
With Intersect(rTarget.EntireRow, listObj.ListColumns(1).DataBodyRange)
    .FormatConditions.Add Type:=xlExpression, Formula1:="=$T5=""Black"""
    With .FormatConditions(2)
        .StopIfTrue = False
        With .Font
            .Bold = False
            .Italic = False
            .Strikethrough = False
            .Color = clrBlack
            .TintAndShade = 0
        End With
    End With

    .FormatConditions.Add Type:=xlExpression, Formula1:="=$T5=""Red"""
    With .FormatConditions(3)
        .StopIfTrue = False
        With .Font
            .Bold = False
            .Italic = False
            .Strikethrough = False
            .Color = clrRed
            .TintAndShade = 0
        End With
    End With
    
     .FormatConditions.Add Type:=xlExpression, Formula1:="=$T5=""Green"""
    With .FormatConditions(4)
        .StopIfTrue = False
        With .Font
            .Bold = False
            .Italic = False
            .Strikethrough = False
            .Color = clrGreen
            .TintAndShade = 0
        End With
    End With
    
    .FormatConditions.Add Type:=xlExpression, Formula1:="=$T5=""Orange"""
    With .FormatConditions(5)
        .StopIfTrue = False
        With .Font
            .Bold = False
            .Italic = False
            .Strikethrough = False
            .Color = clrOrange
            .TintAndShade = 0
        End With
    End With
End With

'Format Description
With Intersect(rTarget.EntireRow, listObj.ListColumns(5).DataBodyRange)
    .FormatConditions.Add Type:=xlExpression, Formula1:="=$U5=""Black"""
    With .FormatConditions(2)
        .StopIfTrue = False
        With .Font
            .Bold = False
            .Italic = False
            .Strikethrough = False
            .Color = clrBlack
            .TintAndShade = 0
        End With
    End With
    .FormatConditions.Add Type:=xlExpression, Formula1:="=$U5=""Red"""
    With .FormatConditions(3)
        .StopIfTrue = False
        With .Font
            .Bold = False
            .Italic = False
            .Strikethrough = False
            .Color = clrRed
            .TintAndShade = 0
        End With
    End With
     .FormatConditions.Add Type:=xlExpression, Formula1:="=$U5=""Green"""
    With .FormatConditions(4)
        .StopIfTrue = False
        With .Font
            .Bold = False
            .Italic = False
            .Strikethrough = False
            .Color = clrGreen
            .TintAndShade = 0
        End With
    End With
    .FormatConditions.Add Type:=xlExpression, Formula1:="=$U5=""Orange"""
    With .FormatConditions(5)
        .StopIfTrue = False
        With .Font
            .Bold = False
            .Italic = False
            .Strikethrough = False
            .Color = clrOrange
            .TintAndShade = 0
        End With
    End With
End With

'Format Location
With Intersect(rTarget.EntireRow, listObj.ListColumns(11).DataBodyRange)
    .FormatConditions.Add Type:=xlExpression, Formula1:="=$V5=""Black"""
    With .FormatConditions(2)
        .StopIfTrue = False
        With .Font
            .Bold = False
            .Italic = False
            .Strikethrough = False
            .Color = clrBlack
            .TintAndShade = 0
        End With
    End With
    .FormatConditions.Add Type:=xlExpression, Formula1:="=$V5=""Red"""
    With .FormatConditions(3)
        .StopIfTrue = False
        With .Font
            .Bold = False
            .Italic = False
            .Strikethrough = False
            .Color = clrRed
            .TintAndShade = 0
        End With
    End With
    .FormatConditions.Add Type:=xlExpression, Formula1:="=$V5=""Green"""
    With .FormatConditions(4)
        .StopIfTrue = False
        With .Font
            .Bold = False
            .Italic = False
            .Strikethrough = False
            .Color = clrGreen
            .TintAndShade = 0
        End With
    End With
    .FormatConditions.Add Type:=xlExpression, Formula1:="=$V5=""Orange"""
    With .FormatConditions(5)
        .StopIfTrue = False
        With .Font
            .Bold = False
            .Italic = False
            .Strikethrough = False
            .Color = clrOrange
            .TintAndShade = 0
        End With
    End With
End With

End Sub
Code: 
Option Explicit

Sub SetPrevRowCFRules()

Set listObj = ws.ListObjects(1)

'Format Expired
With listObj.DataBodyRange
    .FormatConditions.Delete
    .FormatConditions.Add Type:=xlExpression, Formula1:="=$T5=""Gray"""
    With .FormatConditions(1)
        .StopIfTrue = False
        With .Font
            .Bold = False
            .Italic = False
            .Strikethrough = True
            .Color = clrGray
            .TintAndShade = 0
        End With
    End With
End With

'Format Work Order
With listObj.ListColumns(1).DataBodyRange
    .FormatConditions.Add Type:=xlExpression, Formula1:="=$T5=""Black"""
    With .FormatConditions(2)
        .StopIfTrue = False
        With .Font
            .Bold = False
            .Italic = False
            .Strikethrough = False
            .Color = clrBlack
            .TintAndShade = 0
        End With
    End With
    .FormatConditions.Add Type:=xlExpression, Formula1:="=$T5=""Red"""
    With .FormatConditions(3)
        .StopIfTrue = False
        With .Font
            .Bold = False
            .Italic = False
            .Strikethrough = False
            .Color = clrRed
            .TintAndShade = 0
        End With
    End With
     .FormatConditions.Add Type:=xlExpression, Formula1:="=$T5=""Green"""
    With .FormatConditions(4)
        .StopIfTrue = False
        With .Font
            .Bold = False
            .Italic = False
            .Strikethrough = False
            .Color = clrGreen
            .TintAndShade = 0
        End With
    End With
    .FormatConditions.Add Type:=xlExpression, Formula1:="=$T5=""Orange"""
    With .FormatConditions(5)
        .StopIfTrue = False
        With .Font
            .Bold = False
            .Italic = False
            .Strikethrough = False
            .Color = clrOrange
            .TintAndShade = 0
        End With
    End With
End With

'Format Description
With listObj.ListColumns(5).DataBodyRange
    .FormatConditions.Add Type:=xlExpression, Formula1:="=$U5=""Black"""
    With .FormatConditions(2)
        .StopIfTrue = False
        With .Font
            .Bold = False
            .Italic = False
            .Strikethrough = False
            .Color = clrBlack
            .TintAndShade = 0
        End With
    End With
    .FormatConditions.Add Type:=xlExpression, Formula1:="=$U5=""Red"""
    With .FormatConditions(3)
        .StopIfTrue = False
        With .Font
            .Bold = False
            .Italic = False
            .Strikethrough = False
            .Color = clrRed
            .TintAndShade = 0
        End With
    End With
     .FormatConditions.Add Type:=xlExpression, Formula1:="=$U5=""Green"""
    With .FormatConditions(4)
        .StopIfTrue = False
        With .Font
            .Bold = False
            .Italic = False
            .Strikethrough = False
            .Color = clrGreen
            .TintAndShade = 0
        End With
    End With
    .FormatConditions.Add Type:=xlExpression, Formula1:="=$U5=""Orange"""
    With .FormatConditions(5)
        .StopIfTrue = False
        With .Font
            .Bold = False
            .Italic = False
            .Strikethrough = False
            .Color = clrOrange
            .TintAndShade = 0
        End With
    End With
End With

'Format Location
With listObj.ListColumns(11).DataBodyRange 'Location HC, Col 22
    .FormatConditions.Add Type:=xlExpression, Formula1:="=$V5=""Black"""
    With .FormatConditions(2)
        .StopIfTrue = False
        With .Font
            .Bold = False
            .Italic = False
            .Strikethrough = False
            .Color = clrBlack
            .TintAndShade = 0
        End With
    End With
    .FormatConditions.Add Type:=xlExpression, Formula1:="=$V5=""Red"""
    With .FormatConditions(3)
        .StopIfTrue = False
        With .Font
            .Bold = False
            .Italic = False
            .Strikethrough = False
            .Color = clrRed
            .TintAndShade = 0
        End With
    End With
     .FormatConditions.Add Type:=xlExpression, Formula1:="=$V5=""Green"""
    With .FormatConditions(4)
        .StopIfTrue = False
        With .Font
            .Bold = False
            .Italic = False
            .Strikethrough = False
            .Color = clrGreen
            .TintAndShade = 0
        End With
    End With
    .FormatConditions.Add Type:=xlExpression, Formula1:="=$V5=""Orange"""
    With .FormatConditions(5)
        .StopIfTrue = False
        With .Font
            .Bold = False
            .Italic = False
            .Strikethrough = False
            .Color = clrOrange
            .TintAndShade = 0
        End With
    End With
End With

End Sub

Code: 
Option Explicit

Sub SetCFRules()

Set ws = ActiveSheet
Set listObj = ws.ListObjects(1)

'Format Expired
With listObj.DataBodyRange
    .FormatConditions.Delete
    .FormatConditions.Add Type:=xlExpression, Formula1:="=$T5=""Gray"""
    With .FormatConditions(1)
        .StopIfTrue = False
        With .Font
            .Bold = False
            .Italic = False
            .Strikethrough = True
            .Color = clrGray
            .TintAndShade = 0
        End With
    End With
End With

'Format Work Order
With listObj.ListColumns(1).DataBodyRange 'Work Order HC, Col 20
    .FormatConditions.Add Type:=xlExpression, Formula1:="=$T5=""Black"""
    With .FormatConditions(2)
        .StopIfTrue = False
        With .Font
            .Bold = False
            .Italic = False
            .Strikethrough = False
            .Color = clrBlack
            .TintAndShade = 0
        End With
    End With
    .FormatConditions.Add Type:=xlExpression, Formula1:="=$T5=""Red"""
    With .FormatConditions(3)
        .StopIfTrue = False
        With .Font
            .Bold = False
            .Italic = False
            .Strikethrough = False
            .Color = clrRed
            .TintAndShade = 0
        End With
    End With
     .FormatConditions.Add Type:=xlExpression, Formula1:="=$T5=""Green"""
    With .FormatConditions(4)
        .StopIfTrue = False
        With .Font
            .Bold = False
            .Italic = False
            .Strikethrough = False
            .Color = clrGreen
            .TintAndShade = 0
        End With
    End With
    .FormatConditions.Add Type:=xlExpression, Formula1:="=$T5=""Orange"""
    With .FormatConditions(5)
        .StopIfTrue = False
        With .Font
            .Bold = False
            .Italic = False
            .Strikethrough = False
            .Color = clrOrange
            .TintAndShade = 0
        End With
    End With
End With

'Format Description
With listObj.ListColumns(5).DataBodyRange 'Description HC, Col 21
    .FormatConditions.Add Type:=xlExpression, Formula1:="=$U5=""Black"""
    With .FormatConditions(2)
        .StopIfTrue = False
        With .Font
            .Bold = False
            .Italic = False
            .Strikethrough = False
            .Color = clrBlack
            .TintAndShade = 0
        End With
    End With
    .FormatConditions.Add Type:=xlExpression, Formula1:="=$U5=""Red"""
    With .FormatConditions(3)
        .StopIfTrue = False
        With .Font
            .Bold = False
            .Italic = False
            .Strikethrough = False
            .Color = clrRed
            .TintAndShade = 0
        End With
    End With
     .FormatConditions.Add Type:=xlExpression, Formula1:="=$U5=""Green"""
    With .FormatConditions(4)
        .StopIfTrue = False
        With .Font
            .Bold = False
            .Italic = False
            .Strikethrough = False
            .Color = clrGreen
            .TintAndShade = 0
        End With
    End With
    .FormatConditions.Add Type:=xlExpression, Formula1:="=$U5=""Orange"""
    With .FormatConditions(5)
        .StopIfTrue = False
        With .Font
            .Bold = False
            .Italic = False
            .Strikethrough = False
            .Color = clrOrange
            .TintAndShade = 0
        End With
    End With
End With

'Format Location
With listObj.ListColumns(11).DataBodyRange 'Location HC, Col 22
    .FormatConditions.Add Type:=xlExpression, Formula1:="=$V5=""Black"""
    With .FormatConditions(2)
        .StopIfTrue = False
        With .Font
            .Bold = False
            .Italic = False
            .Strikethrough = False
            .Color = clrBlack
            .TintAndShade = 0
        End With
    End With
    .FormatConditions.Add Type:=xlExpression, Formula1:="=$V5=""Red"""
    With .FormatConditions(3)
        .StopIfTrue = False
        With .Font
            .Bold = False
            .Italic = False
            .Strikethrough = False
            .Color = clrRed
            .TintAndShade = 0
        End With
    End With
     .FormatConditions.Add Type:=xlExpression, Formula1:="=$V5=""Green"""
    With .FormatConditions(4)
        .StopIfTrue = False
        With .Font
            .Bold = False
            .Italic = False
            .Strikethrough = False
            .Color = clrGreen
            .TintAndShade = 0
        End With
    End With
    .FormatConditions.Add Type:=xlExpression, Formula1:="=$V5=""Orange"""
    With .FormatConditions(5)
        .StopIfTrue = False
        With .Font
            .Bold = False
            .Italic = False
            .Strikethrough = False
            .Color = clrOrange
            .TintAndShade = 0
        End With
    End With
End With

End Sub

The module "SetCFRules" sets the conditional formatting for the entire table when called. The modules, "SetPrevRowCFRules and "SetActiveRowCFRules" are triggered by a selection change by the user.
 
Upvote 0
Solution
You must log in or register to reply here.

Similar threads

J
  • Question Question
Conditional formatting Coloring due dates
Replies
4
Views
206
JAZ91
J
H
  • Solved
VBA Font Conditional Formatting based on cell value change - but it doesn't trigger
Replies
3
Views
90
hcgood
H
K
  • Solved
highlight numbers
Replies
7
Views
461
MARK858
MARK858
Q
  • Solved
Using VBA instead of Conditional formatting
2
Replies
12
Views
869
Micron
M
K
  • Question Question
Variable losing set Range
Replies
8
Views
455
MARK858
MARK858

Forum statistics

Threads
1,224,818
Messages
6,181,152
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top