Worksheet_Change that considers several cell values

[JBM91]

Hi experts,

I have a sheet that is divided into days and weeks, with data populated below. I'd like to be able to hide weeks (columns) that does not correspond to user-specified cell values in G18 and G19 - i.e. so the user in my case will have the option to only see two weeks at a time. To do this, I have the following formula:

Private Sub Worksheet_Change(ByVal Target As Range)


Dim cel As Range, Headers As Range
Dim s As String

Set Headers = Range("i1:ABJ1")

If Not Intersect(Target, Range("G18:G19")) Is Nothing Then
s = Target.Value
Application.ScreenUpdating = False
If s = "" Then
Headers.EntireColumn.Hidden = False

Else

For Each cel In Headers
cel.EntireColumn.Hidden = Not cel.Value = s
Next cel
End If
Application.ScreenUpdating = True

End If

End Sub

The problem is that it will ONLY consider the range where a change has been made when executing the command. So if I want to see, say, weeks 3 and 4, I'll enter 3 in range C18, which will then hide all columns (weeks) that isn't week 3. However, when I then type 4 in C19, it will take it as I now want to only see columns for week 4 instead and hide everything else, including week 3.

Is there a way to work around that, so that the macro will consider BOTH C18 and C19 whenever a change is made to either one of them?

Thanks in advance!

[PCI]

@JPM91, welcome ... but ... take some minutes to read the forum's rules, particularly the one concerning code tags ...!

[AlphaFrog]

Please Login or Register  to view this content.

[JBM91]

Please Login or Register  to view this content.

Thank you so much, Alpha - that's exactly what I was looking for!