VBA IF cell specific text then automatically hide column in another sheet (I'm so Lost!)

[SledDave]

Hey all,

I've been trying to do this for a few days now and I'm not sure what I'm doing wrong. Admittedly I'm not the best at VBA and have probably skipped some crucial detail.
Anyway, my sheet is a risk assessment for events. Once risks are selected, Cell C22 changes from "" to either "Low" "Medium" "High" or "Very High":

=IFERROR(VLOOKUP(E4,'Event Categorisation Working'!I4:M10,5,1),"Please complete all fields in Event Information")

On another sheet, I have columns for Low to Very High, and depending on the text of Cell C22, I'd like for the columns to hide.
(If "Low" then hide columns D E and F, if "High" then hide C D and F, etc)

I have tried a lot of bad code, including very basic IFs in Module 1:

Sub Catagory_Column_Hide()

If Worksheet("On-Sale").Range("C22") = "Low" Then
Worksheet("On-Sale Deployment").Range("D:F").EntireColumn.Hidden = True
Else
If Worksheet("On-Sale").Range("C22") = "Medium" Then
Worksheet("On-Sale Deployment").Range("C:C").EntireColumn.Hidden = True
Worksheet("On-Sale Deployment").Range("E:F").EntireColumn.Hidden = True
Else
If Worksheet("On-Sale").Range("C22") = "High" Then
Worksheet("On-Sale Deployment").Range("C:D").EntireColumn.Hidden = True
Worksheet("On-Sale Deployment").Range("F:F").EntireColumn.Hidden = True
Else
If Worksheet("On-Sale").Range("C22") = "Very High" Then
Worksheet("On-Sale Deployment").Range("C:E").EntireColumn.Hidden = True

End If

End Sub

I eventually wrote something that if I hit the Green Play button for running the code, the columns did hide, but it never worked automatically.
I'm also sure I should be putting the code in a different place than 'Module 1' but I'm a bit lost as to what Private Sub and Sub differences are, and why things need to be called "Worksheet_Change" etc.
I've tried too many splices of code to list them here, so it's probably best if I start from the beginning again.

Any help would be hugely appreciated. I assumed this would be easy but like I said, I think I'm missing something basic.

Cheers!

 

[Artik]

Since the value of cell C22 is the result of a formula, use the Worksheet_Calculate event to monitor the change in this cell.
The following code should be placed in the On-Sale worksheet module, because the monitored cell C22 is located there.

Option Explicit

Dim mvOldVal As Variant


Private Sub Worksheet_Calculate()
    If mvOldVal <> Me.Range("C22").Value Then
        Application.Calculation = xlCalculationManual
        
        mvOldVal = Me.Range("C22").Value
        
        With Worksheets("On-Sale Deployment")
            .Columns("C:F").Hidden = True

            Select Case mvOldVal
                Case "Low"
                    .Columns("C").Hidden = False
                Case "Medium"
                    .Columns("D").Hidden = False
                Case "High"
                    .Columns("E").Hidden = False
                Case "Very High"
                    .Columns("F").Hidden = False
            End Select
        End With
        
        Application.Calculation = xlCalculationAutomatic
    End If
End Sub

Artik

 

[SledDave]

Since the value of cell C22 is the result of a formula, use the Worksheet_Calculate event to monitor the change in this cell.
The following code should be placed in the On-Sale worksheet module, because the monitored cell C22 is located there.

Option Explicit

Dim mvOldVal As Variant


Private Sub Worksheet_Calculate()
    If mvOldVal <> Me.Range("C22").Value Then
        Application.Calculation = xlCalculationManual
       
        mvOldVal = Me.Range("C22").Value
       
        With Worksheets("On-Sale Deployment")
            .Columns("C:F").Hidden = True

            Select Case mvOldVal
                Case "Low"
                    .Columns("C").Hidden = False
                Case "Medium"
                    .Columns("D").Hidden = False
                Case "High"
                    .Columns("E").Hidden = False
                Case "Very High"
                    .Columns("F").Hidden = False
            End Select
        End With
       
        Application.Calculation = xlCalculationAutomatic
    End If
End Sub

Artik

Thank you so much for your reply!

I've added the code into the On-Sale Worksheet module, but I've never seen lines of code above the sub name before. Is this correct? In the module it looks like I've copied in 2 different subs:

The code hides everything okay but doesn't do anything automatically, nor does it unhide as it should. Though I've probably copied it in wrong!

 

[SledDave]

Since the value of cell C22 is the result of a formula, use the Worksheet_Calculate event to monitor the change in this cell.
The following code should be placed in the On-Sale worksheet module, because the monitored cell C22 is located there.

Option Explicit

Dim mvOldVal As Variant


Private Sub Worksheet_Calculate()
    If mvOldVal <> Me.Range("C22").Value Then
        Application.Calculation = xlCalculationManual
       
        mvOldVal = Me.Range("C22").Value
       
        With Worksheets("On-Sale Deployment")
            .Columns("C:F").Hidden = True

            Select Case mvOldVal
                Case "Low"
                    .Columns("C").Hidden = False
                Case "Medium"
                    .Columns("D").Hidden = False
                Case "High"
                    .Columns("E").Hidden = False
                Case "Very High"
                    .Columns("F").Hidden = False
            End Select
        End With
       
        Application.Calculation = xlCalculationAutomatic
    End If
End Sub

Artik

Are the Option Explicit and Dim mvOldVal as varient supposed to be above the name of the sheet?