VBA explanation please.

[DButler]

Can someone explain what this code is doing? thanks in advance.

[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]Private Sub Worksheet_Change(ByVal Target As Range)
'Updateby Extendoffice 20160725
    Dim xCell As Range
    If Target.Address <> Range("C54").Address Then Exit Sub
    Application.ScreenUpdating = False
    For Each xCell In Range("D7:S7")
        xCell.EntireColumn.Hidden = (xCell.Value > Target.Value)
    Next
    Application.ScreenUpdating = True
End Sub[/FONT]

 

[Joe4]

If cell C54 is manually updated, the code will run and compare each of the values in the range D7:S7 to the new value in cell C54.
For each of those cells, if their value is greater than the value in cell C54, it will hide that entire column.

 

[DButler]

Thanks for the reply Joe4!!!

I need to apply this code to the rest of the sheets in the workbook as well. However, the range to be evaluated to C54 of sheet1 on the rest of the sheets is E2:T2. How can I apply this to the remaining sheets? will it have to be entered in each sheet, or is there a common way to apply it?

 

[Joe4]

The Worksheet_Change code is specific to the sheet module that the code is located in. So it would need to be placed in each sheet module.
Though, I think there might be a way to do it across all sheets using Workbook_SheetChange.
See here: https://www.mrexcel.com/forum/excel-questions/627720-workbook-change-event.html

 

[DButler]

I'll look at that, thank you!!

 

[Joe4]

You are welcome.

 

[DButler]

ok, Being that I am not the sharpest knife in the drawer, would it be easier if I were to send the sheet to you? If you have time to look at it?

 

[Joe4]

OK, in re-reading it, I think I originally misinterpretted your follow-up request. It sounds like you are only concerned about changes to C54 on Sheet1 (not every sheet), but want it to affect every sheet.
If that is the case, then you can continue to use the Worksheet_Change event procedure where it is, just use this variation, which loops through all sheets:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim orgVal As Double
    Dim xCell As Range
    Dim ws As Worksheet

'   If cell C54 not updated on first sheet, exit sub
    If Target.Address <> Range("C54").Address Then Exit Sub
    
'   Capture value of C54
    orgVal = Range("C54").Value
    
    Application.ScreenUpdating = False
'   Loop through all sheets
    For Each ws In Worksheets
        With ws
            For Each xCell In .Range("E2:T2")
                xCell.EntireColumn.Hidden = (xCell.Value > orgVal)
            Next xCell
        End With
    Next ws
    Application.ScreenUpdating = True

End Sub

 

[DButler]

You are the MAN!!! works like a charm. Thanks so much!!!

 

[Joe4]

You are welcome.
Glad I was able to help!