Highlight Unmatched Headings in Excel with VBA

Objective

To ensure consistency between two sets of headings in an Excel file, highlighting any discrepancies in red using VBA.

Overview

This guide will include:

1. VBA code to detect unmatched headings.
2. Instructions on how to implement this code in Excel.
3. Explanation of how the code works.

VBA Code

The following VBA code will check for unmatched headings in a master sheet and highlight them in red.

Sub HighlightUnmatchedHeadings()
    Dim ws As Worksheet
    Dim headerRange As Range
    Dim cell As Range
    Dim unmatched As Boolean
    
    ' Set the worksheet to the master sheet
    Set ws = ThisWorkbook.Sheets("Master") ' Change "Master" to your actual sheet name
    ' Specify the range where headings are located
    Set headerRange = ws.Rows(1) ' Adjust if headers are in a different row
    
    ' Clear previous highlights
    headerRange.Interior.ColorIndex = xlNone
    
    ' Loop through each cell in the header range
    For Each cell In headerRange.Cells
        ' Check if the cell is empty or does not match expected heading
        If IsEmpty(cell) Or Not ValidateHeading(cell.Value) Then
            cell.Interior.Color = vbRed ' Highlight unmatched in red
        End If
    Next cell
End Sub

Function ValidateHeading(heading As String) As Boolean
    Dim validHeadings As Variant
    validHeadings = Array("ID", "Name", "Date", "Value") ' Add other expected headings as necessary
    
    ' Check if the heading matches any of the valid headings
    ValidateHeading = Not IsError(Application.Match(heading, validHeadings, 0))
End Function

Implementation Steps

1. Open Excel: Open the workbook where you want to check the headings.
2. Access the VBA Editor:
   • Press ALT + F11 to open the VBA editor.
3. Insert a New Module:
   • Right-click on any of the items for your workbook and select Insert > Module.
4. Paste the Code:
   • Copy the provided VBA code and paste it into the module window.
5. Edit the Worksheet Name:
   • Change "Master" in the code to the actual name of your worksheet if different.
6. Add Expected Headings:
   • Modify the validHeadings array in the ValidateHeading function to include all headers you expect.
7. Run the Macro:
   • Close the VBA editor, return to Excel, press ALT + F8, select HighlightUnmatchedHeadings and click Run.

Explanation of the Code

• Worksheet and Range Declaration: The code defines the relevant worksheet and the range that contains the headings.
• Clearing Previous Highlights: The prior interior color of the header range is reset to ensure a fresh start.
• Looping through Headings: For each cell in the header row, the code checks if the cell is either empty or does not match the expected headings.
• Highlighting Unmatched Headings: If a heading does not match, it is highlighted in red.
• Validation Function: The ValidateHeading function checks the given heading against a predefined list of valid headings. It returns True if the heading is valid and False otherwise.

Conclusion

The provided VBA code enables the identification and highlighting of unmatched headings in your Excel master file effectively. Users are encouraged to customize the expected headings list and the range as per their specific requirements for optimal results. For further training on Excel and VBA, consider exploring the Enterprise DNA Platform.