Help -needed call named range based on cell value

[maorlov]

Hi all,

I have a problem and since I am not that great with VBA hope that someone can help me :).

I would like to have a code that calls specific named range (e.g. "UK" or range "AM:BA") based on the cell value. So if e.g. $L$5=1 then show AM:BA in that sheet and hide everything else?

Any help is much appreciated!

Thank you!

[mdmackillop]

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$A$1" Then Exit Sub
On Error GoTo Exits
Application.EnableEvents = False
'unhide all columns
Columns("A:XFD").Hidden = False
Select Case Target
Case 1
Columns("B:H").Hidden = True
Columns("Z:XFD").Hidden = True
Case 2
Columns("B:D").Hidden = True
Columns("K:Z").Hidden = True
Case 3
Columns("B:XFD").Hidden = False
End Select
Exits:
Application.EnableEvents = True
End Sub

[maorlov]

Thank you! I have tried adding it into my sheet but sth still seems missing. So my range cell is L5 and I tried copying your code and adding "Select Case Range("L5")" instead of "Select Case Target ", do you know what is wrong in my code?

[mdmackillop]

Please post your code

[maorlov]

So I want show/hide columns based on the value of the cell L5. It is your code, I have just tried to change range.


Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$A$1" Then Exit Sub
On Error GoTo Exits
Application.EnableEvents = False
'unhide all columns
Columns("A:XFD").Hidden = False
Select Case Range ("L5")
Case 1
Columns("B:H").Hidden = True
Columns("Z:XFD").Hidden = True
Case 2
Columns("B:D").Hidden = True
Columns("K:Z").Hidden = True
Case 3
Columns("B:XFD").Hidden = False
End Select
Exits:
Application.EnableEvents = True
End Sub

[mdmackillop]

If you step through the code you'll find that this is the issue

If Target.Address <> "$A$1" Then Exit Sub