October 21, 2020

Automatically Highlight Column Header

How to automatically highlight column header based on date or text using VBA.

Watch the video below:

Automatically Highlight Column Header

A user wants to open a file and highlight today’s date from the header columns. Her column headers are made up of dates. Every time she opens the file she wishes to highlight today’s date header and start working below this header.

Here’s the complete VBA or macro code to highlight the required column:

Private Sub Workbook_Open()
‘Dim x As Date
Dim x As String
Dim found As Boolean

‘x = Date ‘Use for columns with headers having dates. Remove apostrophe.
x = InputBox(“Enter a string for the header to be selected”) ‘ Use for headers with text. Add apostrophe if you have dates in headers.


Do Until IsEmpty(ActiveCell)

If ActiveCell.Value = x Then

found = True


ActiveCell.Font.Color = vbRed

Exit Do

End If

ActiveCell.Offset(0, 1).Select


If found = True Then
MsgBox “Value found in cell ” & ActiveCell.Address
MsgBox “Value not found”
End If

End Sub

The above code executes to highlight the column headers depending on data types dates and text as soon as the workbook opens.

Automatically Highlight Column Header with Dates or Text