How to automatically highlight column header based on date or text using VBA.
Watch the video below:
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.
Range(“A3”).Select
Do Until IsEmpty(ActiveCell)
If ActiveCell.Value = x Then
found = True
ActiveCell.Select
ActiveCell.Font.Color = vbRed
Exit Do
End If
ActiveCell.Offset(0, 1).Select
Loop
If found = True Then
MsgBox “Value found in cell ” & ActiveCell.Address
Else
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.
