Code for conditional formula
 

I have the following code to enable a hidden column(G) to be unhidden, should
a certain category in the previous column(B) be chosen. I took that from an
online example. However what I trying to achieve without success is the
following: I would like column(G) automatically to be hidden again, once the
next cell in the next column has been moved to. Any help would be greatly
appreciated.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 5 And Target.Value = "Locators" Then
Columns("G").Hidden = False
ElseIf Target.Column = 5 And Target.Value = "Detectors" Then
Columns("G").Hidden = False
ElseIf Target.Column = 5 And Target.Value = "Survey/Navigation Equip." Then
Columns("G").Hidden = False
ElseIf Target.Column = 5 And Target.Value = "Machinery" Then
Columns("G").Hidden = False
ElseIf Target.Column = 5 And Target.Value = "Medical Equip. Cap. Assets"
Then
Columns("G").Hidden = False
ElseIf Target.Column = 5 And Target.Value = "Weapons" Then
Columns("G").Hidden = False
ElseIf Target.Column = 5 And Target.Value = "Desktops" Then
Columns("G").Hidden = False
ElseIf Target.Column = 5 And Target.Value = "Laptops" Then
Columns("G").Hidden = False
ElseIf Target.Column = 5 And Target.Value = "Printers" Then
Columns("G").Hidden = False
ElseIf Target.Column = 5 And Target.Value = "Scanners" Then
Columns("G").Hidden = False
ElseIf Target.Column = 5 And Target.Value = "Digital Cameras" Then
Columns("G").Hidden = False
ElseIf Target.Column = 5 And Target.Value = "Radios" Then
Columns("G").Hidden = False
ElseIf Target.Column = 5 And Target.Value = "Sat Phones" Then
Columns("G").Hidden = False
ElseIf Target.Column = 5 And Target.Value = "Mobile Phones" Then
Columns("G").Hidden = False
ElseIf Target.Column = 5 And Target.Value = "Vehicles" Then
Columns("G").Hidden = False

End If
End Sub

Code for conditional formula
 

You could put this in the same sheet module as your other event handler.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Column < 5 Then _
Columns("G").Hidden = True
End Sub


Also, I think you can shorten your existing code to use only one IF
statement and a Case statement.


Option Compare Text 'Case insensitive - if you leave this in, it should go
at the
'top of your code module.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 5 Then
Select Case Target.Value
Case "Locators", "Detectors", "Survey/Navigation Equip.", _
"Machinery", "Medical Equip.Cap.Assets", "Weapons", _
"Desktops", "Laptops", "Printers", "Scanners", _
"Digital Cameras", "Radios", "Sat Phones", "Mobile Phones", _
"Vehicles"
Columns("G").Hidden = False
End Select
End If
End Sub

"Frederic" wrote:

I have the following code to enable a hidden column(G) to be unhidden, should
a certain category in the previous column(B) be chosen. I took that from an
online example. However what I trying to achieve without success is the
following: I would like column(G) automatically to be hidden again, once the
next cell in the next column has been moved to. Any help would be greatly
appreciated.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 5 And Target.Value = "Locators" Then
Columns("G").Hidden = False
ElseIf Target.Column = 5 And Target.Value = "Detectors" Then
Columns("G").Hidden = False
ElseIf Target.Column = 5 And Target.Value = "Survey/Navigation Equip." Then
Columns("G").Hidden = False
ElseIf Target.Column = 5 And Target.Value = "Machinery" Then
Columns("G").Hidden = False
ElseIf Target.Column = 5 And Target.Value = "Medical Equip. Cap. Assets"
Then
Columns("G").Hidden = False
ElseIf Target.Column = 5 And Target.Value = "Weapons" Then
Columns("G").Hidden = False
ElseIf Target.Column = 5 And Target.Value = "Desktops" Then
Columns("G").Hidden = False
ElseIf Target.Column = 5 And Target.Value = "Laptops" Then
Columns("G").Hidden = False
ElseIf Target.Column = 5 And Target.Value = "Printers" Then
Columns("G").Hidden = False
ElseIf Target.Column = 5 And Target.Value = "Scanners" Then
Columns("G").Hidden = False
ElseIf Target.Column = 5 And Target.Value = "Digital Cameras" Then
Columns("G").Hidden = False
ElseIf Target.Column = 5 And Target.Value = "Radios" Then
Columns("G").Hidden = False
ElseIf Target.Column = 5 And Target.Value = "Sat Phones" Then
Columns("G").Hidden = False
ElseIf Target.Column = 5 And Target.Value = "Mobile Phones" Then
Columns("G").Hidden = False
ElseIf Target.Column = 5 And Target.Value = "Vehicles" Then
Columns("G").Hidden = False

End If
End Sub