Thread: Hide Unhide Columns on certain cell value

Hi,

I need to write a macro to hide and unhide columns in multiple speadsheets on particular cell value (say Yes/No).

I have 6 excel worksheets - Sheet 1 - Sheet 6.
In first worksheet (Sheet 1), I have given a condition to be provided by user in terms of "Yes" or "No".

In case, if in the first worksheet the condition provided is "Yes", I have to hide Coumns F, G, H and K in other five worksheets.

Hi,

Try this:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim wsEachSheet As Worksheet
    If Intersect(Target, Range("A1")) Is Nothing Then Exit Sub
    For Each wsEachSheet In ThisWorkbook.Worksheets
        If wsEachSheet.Name <> "Sheet1" Then
            If UCase(Range("A1").Value) = "YES" Then
                wsEachSheet.Range("F:F,G:G,H:H,K:K").EntireColumn.Hidden = True
            ElseIf UCase(Range("A1").Value) = "NO" Then
                wsEachSheet.Range("F:F,G:G,H:H,K:K").EntireColumn.Hidden = False
            End If
        End If
    Next wsEachSheet
End Sub

Right click on the Sheet1's tab and select View Code. Change A1 to the cell that will have Yes/No in it.

Dom

Try this:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim wsEachSheet As Worksheet
    If Intersect(Target, Range("A1")) Is Nothing Then Exit Sub
    For Each wsEachSheet In Sheets(Array("Sheet2", "Sheet3", "Sheet4", "Sheet5"))
            If UCase(Range("A1").Value) = "YES" Then
                wsEachSheet.Range("F:F,G:G,H:H,K:K").EntireColumn.Hidden = True
            ElseIf UCase(Range("A1").Value) = "NO" Then
                wsEachSheet.Range("F:F,G:G,H:H,K:K").EntireColumn.Hidden = False
            End If
    Next wsEachSheet
End Sub

Dom

Close, you just needed to change True to False to unhide the columns:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim wsEachSheet As Worksheet
    If Intersect(Target, Range("A1")) Is Nothing Then Exit Sub
    For Each wsEachSheet In Sheets(Array("Sheet2", "Sheet3", "Sheet4", "Sheet5"))
            If UCase(Range("A1").Value) = "YES" Then
                wsEachSheet.Range("F:F,G:G,H:H,K:K").EntireColumn.Hidden = True
                wsEachSheet.Range("89:99").EntireRow.Hidden = True
            ElseIf UCase(Range("A1").Value) = "NO" Then
                wsEachSheet.Range("F:F,G:G,H:H,K:K").EntireColumn.Hidden = False
                wsEachSheet.Range("89:99").EntireRow.Hidden = False
            End If
    Next wsEachSheet
End Sub

Please remember to use code tags when posting code to the forum.

Dom

Hi Dom,

Thanks a lot for your time and support !

I am a beginner in Excel Macros. Could you please suggest me from where I should start ?
Does this forum provides some tutorials or docs which can help me.