VBA Code to Hide columns based on selection on prior sheet

Silver1379 · #1 04-15-2015, 08:40 AM

Hi, I am working on a vba code that hides columns on tab 2 based on a section in tab 1. The code I have is below, only the CCB Law - Mortgage Banking section works as it should. Any help would be greatly appreciated!
What I need the code to do is, if the user selects CCB Non Law from the drop down on tab 1, then hide columns D:E on tab 2
If the User selects CCB Law- CLLRT then it hides Column E
If the user selects CCB Law - Mortgage Banking the it hides Column D

Code:

Private Sub Worksheet_Change(ByVal Target As Range)
With ActiveSheet
Sheets(2).Unprotect Password:="SoraAX14"
 If Target.Address = "$C$42" Then
    If Target.Value = "CCB Non Law" Then
      Sheets(2).Columns("D:E").EntireColumn.Hidden = True
        End If
    End If
   If Target.Address = "$C$42" Then
   If Target.Value <> "CCB Non Law" Then
    Sheets(2).Columns("D:E").EntireColumn.Hidden = False
       End If
    End If
  If Target.Address = "$C$42" Then







    If Target.Value = "CCB Law - Mortgage Banking" Then
      Sheets(2).Columns("D:D").EntireColumn.Hidden = True
     End If
    End If
   If Target.Address = "$C$42" Then
   If Target.Value <> "CCB Law - Mortgage Banking" Then
    Sheets(2).Columns("D:D").EntireColumn.Hidden = False
    End If
    End If
      If Target.Address = "$C$42" Then
    If Target.Value = "CCB Law- CLLRT" Then
      Sheets(2).Columns("E:E").EntireColumn.Hidden = True
     End If
    End If
   If Target.Address = "$C$42" Then
   If Target.Value <> "CCB Law- CLLRT" Then
    Sheets(2).Columns("E:E").EntireColumn.Hidden = False
    End If
       Sheets(2).Protect Password:="SoraAX14"
     End If
    
     End With
End Sub

This is the only piece of the code that is working as it should:
If Target.Address = "$C$42" Then
If Target.Value = "CCB Law - Mortgage Banking" Then
Sheets(2).Columns("D

").EntireColumn.Hidden = True
End If
End If
If Target.Address = "$C$42" Then
If Target.Value <> "CCB Law - Mortgage Banking" Then
Sheets(2).Columns("D

").EntireColumn.Hidden = False
End If
End If

I would appreciate any and all help with this.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Hide/Unhide Text Based on Drop Down Selection	gw1500se	Word	3	02-19-2015 12:17 PM
Link listbox (with multiple columns) to bookmark based on selection	dsjk9190	Word VBA	5	01-29-2015 11:28 PM
Show/Hide Text based on Checkbox Selection	tammytran105	Word VBA	7	10-02-2014 04:30 PM
Hide/Unhide a Block of Text Based on Choice Selection	inquirer	Word VBA	7	09-22-2014 04:41 PM