phillipcook
Board Regular
- Joined
- Jun 25, 2015
- Messages
- 87
Hi guys
I have the following macro:
The Macro is close to working. The cells are remaining hidden if the data does not match, however only one column at any time will un-hide (the un-hidden column relates to the last data entered)
on occasions, if the criteria is met, multiple columns should be displayed, but this isn't occurring.
Can anyone please help me figure out why this macro is only allowing one column to ever un-hide?
Cheers in advance.
I have the following macro:
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
If Target.Cells.CountLarge > 1 Then Exit Sub
Set rng = Intersect(Target, Union(Range("E4"), Range("A10:A19")))
If rng Is Nothing Then Exit Sub
Select Case rng.Address(0, 0)
Case "E4"
Columns("J").EntireColumn.Hidden = Not CBool(Target.Value = "BMA - Hay Point Coal Terminal")
Case Else
Columns("K").EntireColumn.Hidden = Not CBool(Target.Value = "Surface Water - Monthly")
Columns("L").EntireColumn.Hidden = Not CBool(Target.Value = "Surface Water - Investigation")
Columns("M").EntireColumn.Hidden = Not CBool(Target.Value = "Potable Water - Investigation")
Columns("N").EntireColumn.Hidden = Not CBool(Target.Value = "Ground Water - Unscheduled No Purge")
Columns("O").EntireColumn.Hidden = Not CBool(Target.Value = "Ground Water - Unscheduled Purge")
Columns("P").EntireColumn.Hidden = Not CBool(Target.Value = "Discharge Water - Daily" Or Target.Value = "Discharge Water - Weekly" Or Target.Value = "Discharge Water - Investigation")
Columns("Q").EntireColumn.Hidden = Not CBool(Target.Value = "Quarterly Contractor Meeting")
Columns("R").EntireColumn.Hidden = Not CBool(Target.Value = "Ground Water - Weekly" Or Target.Value = "Ground Water - Quarterly" Or Target.Value = "Ground Water - 6 Monthly")
Columns("S").EntireColumn.Hidden = Not CBool(Target.Value = "Ground Water - Weekly" Or Target.Value = "Ground Water - Quarterly" Or Target.Value = "Ground Water - 6 Monthly")
End Select
End SubThe Macro is close to working. The cells are remaining hidden if the data does not match, however only one column at any time will un-hide (the un-hidden column relates to the last data entered)
on occasions, if the criteria is met, multiple columns should be displayed, but this isn't occurring.
Can anyone please help me figure out why this macro is only allowing one column to ever un-hide?
Cheers in advance.
Last edited by a moderator:
