Prerequisites:
- Range(“DataRange”)
- Range(“DataTopLeft”)
- Range(“SelectAll”)
- Check that Highlight Color is the same in trigger and Sub
Worksheet_BeforeDoubleClick
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) Dim iFirstRow As Integer Dim iLastRow As Integer iFirstRow = Range("DataTopLeft").Row iLastRow = Range("DataTopLeft").Row + Range("DataRange").Rows.Count - 1 If Target = Range("SelectAll") Then If Range("SelectAll").Interior.Color = vbRed Then Call ChangeEntireColumnToggle(False, Target.Column) Else Call ChangeEntireColumnToggle(True, Target.Column) End If Range("A1").Select End If End Sub
Sub ChangeEntireColumnToggleTEST() Call ChangeEntireColumnToggle(False, 6) End Sub Sub ChangeEntireColumnToggle(blnChoice As Boolean, iColumn As Integer) '*********************************************************** 'Prerequisites: Range("DataRange"), Range("DataTopLeft"), 'Range("SelectAll") to be double clicked, and toggled. ' ' '*********************************************************** Dim rngDataColumn As Range Dim iFirstRow As Integer Dim iLastRow As Integer ' Dim intHighlightColor As Integer ' Dim intHighlightFontColor As Integer iFirstRow = Range("DataTopLeft").Row iLastRow = Range("DataTopLeft").Row + Range("DataRange").Rows.Count - 1 intHighlightColor = vbRed intHighlightFontColor = vbWhite Set rngDataColumn = Range(Cells(iFirstRow, iColumn), Cells(iLastRow, iColumn)) If blnChoice = True Then Range("SelectAll").Interior.Color = intHighlightColor Range("SelectAll").Font.Color = intHighlightFontColor 'cells will be Highlighted by the DirtyCells feature when contents change ''or use Conditional Formatting rngDataColumn.Value = "Yes" Else Range("SelectAll").Interior.Pattern = xlNone Range("SelectAll").Font.Color = vbBlack rngDataColumn.Value = "No" End If End Sub