Worksheet Macro, Change Based on Cell Data, Hide or Show Columns on Different Worksheets

[teksp0rt]

Hello Experts. Can you help me with this Macro please?

I am trying to hide a range of columns and rows on a separate worksheet (Worksheet "R"), when the value of a cell in D8 changes on Worksheet "A" - the sheet where I modify the value. I have been trying different code but cannot get the section in red below to work correctly, which hides or unhides a column on Worksheet R... For reference, I have shortened my Macro, as this is the segment that keeps having errors.

This is written in the Worksheet Object, not in a separate module - if this matters.


-----------------------------------

Private Sub Worksheet_Change(ByVal Target As Range)

Dim IncludeUnitMix As Range

' Set Key Cells To Change

Set IncludeStubPeriod = Range("D8")

' Action for Include Stub Period

If Not Application.Intersect(IncludeStubPeriod, Range(Target.Address)) _
Is Nothing Then


' Hide or Show STUB Column in Model

If Range("D8") = "Yes" Then

Columns("K:K").Select
Selection.EntireColumn.Hidden = False

Sheets("R").Select
Columns("D:D").Select
Selection.EntireColumn.Hidden = True


End If

If Range("D8") = "No" Then

Columns("K:K").Select
Selection.EntireColumn.Hidden = True

End If


End Sub

-----------------------------------------

 

[dmt32]

Hi,
try this update to your code

Private Sub Worksheet_Change(ByVal Target As Range)


    Dim IncludeUnitMix As Range
    
' Set Key Cells To Change
    Set IncludeStubPeriod = Me.Range("D8")
    
' Action for Include Stub Period
    
    If Not Application.Intersect(Target, IncludeStubPeriod) Is Nothing Then
    
' Hide or Show STUB Column in Model
    Me.Columns("K:K").EntireColumn.Hidden = CBool(UCase(Target.Value) = "YES")
    
    Sheets("R").Columns("D:D").EntireColumn.Hidden = CBool(UCase(Target.Value) = "YES")
    
    
    End If




End Sub

Be aware that worksheet change event does not trigger if the value in D8 is changed by formula


Dave