Excel - Hide Columns based on Formula Result rather then Manual Cell Value - VBA

[Excellator]

Right, I'm pretty good at Excel but just needed some clarity on some VBA Code.

Purpose - To hide columns on "Sheet 2" based on the formula result in Cell A7 in "Sheet 1".

For Example: When A7 in Sheet 1 = 1, hide column A in Sheet 2.

So far the coding is per below:

Private Sub Worksheet_change(ByVal Target As Range)
If Target.Address = "$A$7" And Target.Value = 1 Then
Sheets("Data").Range("A:A").EntireColumn.Hidden = True
Else
If Target.Address = "$A$7" And Target.Value <> 1 Then
Sheets("Data").Range("A:A").EntireColumn.Hidden = False
End If
End If

How do I get this so that when A7 is not a typed in number (i.e 1) but is the result of a formula. Basically I want to put in =IF(A8=1,1,0) but this results in the Event not running...

Thanks for your help guys!

[jeffreybrown]

Please take a few minutes and read the forum rules about code tags...

[Excellator]

Sorry will do next time...

[Excellator]

Private Sub Worksheet_change(ByVal Target As Range)
If Target.Address = "$A$7" And Target.Value = 1 Then
Sheets("Data").Range("A:A").EntireColumn.Hidden = True
Else
If Target.Address = "$A$7" And Target.Value <> 1 Then
Sheets("Data").Range("A:A").EntireColumn.Hidden = False
End If
End If

[jeffreybrown]

Try...

Private Sub Worksheet_Change(ByVal Target As Range)
    If Sheets("Sheet1").Range("A7") = 1 Then
        Sheets("Data").Range("A:A").EntireColumn.Hidden = True
    ElseIf Sheets("Sheet1").Range("A7") <> 1 Then
        Sheets("Data").Range("A:A").EntireColumn.Hidden = False
    End If
End Sub

[jeffreybrown]

If you want it to fire off of A7 then you need a calculate event, but because you are changing A8 to get A7 to update then the worksheet change event works fine.

Private Sub Worksheet_Calculate()
    If Sheets("Sheet1").Range("A7") = 1 Then
        Sheets("Data").Range("A:A").EntireColumn.Hidden = True
    ElseIf Sheets("Sheet1").Range("A7") <> 1 Then
        Sheets("Data").Range("A:A").EntireColumn.Hidden = False
    End If
End Sub

[Excellator]

Much appreciated Jeff. Works a treat. Last point, do you know how to involve a range without having to replicate the formula numerous times based on the columns I want to hide, for example, I want A7 to hide Column A which it now does, but if I change B7 to 1, nothing happens to Column B in Sheet 2 and so on..

I've added the formula in above and replicated it underneath a few times changing A7 to B7 and A:A to B:B and so on but this is quite long.. just wondered if there was a quicker range code I could incorporate?

[jeffreybrown]

If I understand you correctly, you would like to hide the column on Sheet - Data which corresponds to the row 7 on Sheet1 where you place a 1.

Example:
If I place a 1 in A7 column A will hide on Sheet - Data and then I place a 1 in B7, column B with hide on Sheet - Data. If I then place a zero or some non 1 vaule in A7, column A will unhide on Sheet - Data but column B is still hidden.

Private Sub Worksheet_change(ByVal Target As Range)
    Dim myCol As Long
    myCol = ActiveCell.Column
    If Sheets("Sheet1").Cells(7, ActiveCell.Column) = 1 Then
        Sheets("Data").Columns(myCol).EntireColumn.Hidden = True
    ElseIf Sheets("Sheet1").Cells(7, ActiveCell.Column) <> 1 Then
        Sheets("Data").Columns(myCol).EntireColumn.Hidden = False
    End If
End Sub