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