Excel VBA: Show or Hide Columns Based on an Intern or Extern Selection
Question details
Automatically show or hide worksheet columns or rows when a user selects Intern or Extern.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A worksheet uses a selection cell to control which information is visible.
- Observed behavior
- Changing the selection should display the relevant columns or rows and hide the others.
Choose a control cell and identify the columns or rows the macro should manage. The examples use A1 for the selection; save the workbook as an Excel Macro-Enabled Workbook (.xlsm) and enable macros when prompted.
Use a Worksheet Change Event to Show or Hide Columns
Use this method when columns have Intern or Extern headers and should respond automatically to the value in A1.
This example checks headers in B1:G1. When A1 contains Intern, it hides columns labeled Extern; when A1 contains Extern, it hides columns labeled Intern. Other columns in the range remain visible.
Enter Intern or Extern in A1, and label the relevant headers in B1:G1 with Intern or Extern. You can use Data > Data Validation to create a drop-down list in A1.
Right-click the worksheet tab, select View Code, and paste the following code into that worksheet's code window: Private Sub Worksheet_Change(ByVal Target As Range) Dim header As Range Dim choice As String If Intersect(Target, Me.Range("A1")) Is Nothing Then Exit Sub choice = Trim$(CStr(Me.Range("A1").Value)) For Each header In Me.Range("B1:G1") header.EntireColumn.Hidden = False If StrComp(choice, "Intern", vbTextCompare) = 0 Then If StrComp(Trim$(CStr(header.Value)), "Extern", vbTextCompare) = 0 Then header.EntireColumn.Hidden = True ElseIf StrComp(choice, "Extern", vbTextCompare) = 0 Then If StrComp(Trim$(CStr(header.Value)), "Intern", vbTextCompare) = 0 Then header.EntireColumn.Hidden = True End If Next header End Sub
Use File > Save As to save the workbook as .xlsm. Return to the worksheet and change A1 between Intern and Extern to test the column visibility.

Hide Rows Based on Cell Fill Color
Use this variation when the worksheet identifies rows by the fill color of cells in column A.
Work With Intern and Extern Data in WPS Spreadsheets
WPS Office is a free, lightweight alternative with a familiar spreadsheet interface. Open compatible Excel files and use filters to view Intern or Extern data as you migrate your everyday spreadsheet work. Excel VBA event macros may not work the same way in WPS and should be tested separately.

Frequently Asked Questions
Why doesn't the macro run when I click A1?
Worksheet_Change runs when the value in A1 changes, not when you merely select the cell. Change the value using the cell or its Data Validation drop-down.
Where should I paste the Worksheet_Change code?
Right-click the relevant sheet tab, select View Code, and paste the procedure into that worksheet's code window. A worksheet can have only one Worksheet_Change procedure.
How do I keep the macro when saving the workbook?
Choose File > Save As and select Excel Macro-Enabled Workbook (.xlsm). Enable macros when prompted after reopening the file.
Can the row macro detect conditional formatting colors?
The example checks the cell's direct Interior fill, so it does not detect colors displayed only by conditional formatting. Consider adding a category column and filtering it instead.
Why are previously hidden columns or rows visible after changing A1?
Both examples unhide their managed range before applying the new rule. Adjust the managed range in the code if it includes columns or rows that should remain hidden independently.




