Hiding Columns based on formula results

[jhennige]

I searched the site and found several similar questions, but I could not find the exact answer to my problem. I expect that this is an easy one.

I have a formula (similar to [=If(a1>b1,0,1)] in the protected cells F2:Q2 that will return either a 1 or a 0. I need a macro that will hide every column that contains a 0. Of course the macro needs to account for the formula and not just the value.

The data is dynamic so the macro needs to run if there are any changes to the formula results in F2:Q2.

The worksheet is protected with a password.

I am using Excel 2000 and Windows 2000.

Any help would be appreciated.

 

[Richie(UK)]

Hi,

The Change code would be pretty similar, something like this:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    Dim rng As Range, rngCell As Range

    If Target.Address <> "$C$30" Then Exit Sub
    'only action of there is a change to A2
    Set rng = Range("F2:Q2")
    Me.Unprotect password:="Letmein"
    'use your password here
    Application.ScreenUpdating = False
    rng.EntireColumn.Hidden = False
    'show all
    For Each rngCell In rng
        If rngCell.Value = 0 Then rngCell.EntireColumn.Hidden = True
        'hide if its a zero
    Next
    Me.Protect password:="Letmein"
    Application.ScreenUpdating = True
    Set rng = Nothing
    'free memory
End Sub

However, did you read the part in my previous post that said "... this will not prevent the UnDo problem as you are still using event macros." ?

 

[jhennige]

That's the code I needed!

Yes, I did read your comment about it not preventing the undo problem, but since the cell that will launch the macro is not changed that often, the problem has been greatly reduced using the Worksheet_Change approach. With the Worksheet_Calculate approach, the Undo function was disabled altogether.

Once again, thank you!