Solution: Update Conditional Formatting Without Calculating Sheet

[Jeffrey Mahoney]

In many of my workbooks with large tables I like to have the active row and column highlighted; it's much easier to see across many rows. I use conditional formatting (CF) with Sub Worksheet_SelectionChange in VBA to highlight the active row and column.

The problem was that I was using Activesheet.Calculate to trigger the CF to change. On sheets with a large number of formulas, this can slow things down. I recently found a new method that makes it faster. I now use EnableFormatConditionsCalculation by flipping it from True to False and back again.

In this case I also have a cell that controls whether the CF updates or not; I named it CalcFollowCB. This cell uses wingding characters to look like a checkbox. Character "o" is unchecked and character "þ" is checked. You don't have to use that. The formulas in the two CF rules are:
Making the current cell yellow
=AND(CalcFollowCB="þ",CELL("Row")=ROW(),CELL("col")=COLUMN())
Making the current row and column yellow slashes. You can remove the portion of the formula that tests if greater than row 7
=AND(CalcFollowCB="þ",CELL("Row")>7,OR(CELL("Col")=COLUMN(),CELL("Row")=ROW()))

If you flip the EnableFormatConditionsCalculation status for the sheet, it replaces having to calculate the sheet.
In the sheet module for your sheet ad this code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  
  'Calculate only when the checkbox is checked
  If Range("CalcFollowCB").Value = "þ" Then
    On Error Resume Next
    ActiveSheet.EnableFormatConditionsCalculation = False
    ActiveSheet.EnableFormatConditionsCalculation = True
    On Error GoTo 0
  End If

End Sub

I also use this code in the sheet module to control the checkbox. If the user double clicks the cell with a named range of "CalcFollowCB", this code will toggle the value.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
  Dim I As Range
    
  Set I = Intersect(Target, Range("CalcFollowCB"))
  If Not I Is Nothing Then
    ToggleCheck I, Cancel
    Exit Sub
  End If
    
End Sub

'Change from Checked to Unchecked and vis-versa.  Return Cancel if provided.  Return toggle Status
Function ToggleCheck(R As Range, Optional Cancel As Boolean) As Boolean
  Application.EnableEvents = False
  Cancel = True
  If R.Value = "þ" Then
    R.Value = "o"
    ToggleCheck = True
  Else
    R.Value = "þ"
    ToggleCheck = False
  End If
  Application.EnableEvents = True
End Function