It kind of works, but only when you click out of the cell, not when you
select a different value from the list.

And now every time you change cells the screen flashes!

"Zone" wrote:

How about this? Copy this code and paste it in the same module as your
Worksheet_Change code.
James

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim targ As Range
Set targ = Target
Worksheet_Change targ
End Sub

"ivory_kitten" wrote in message
...
i have a VBA code to show/hide rows depending on the value of a cell, the
values can either be selected from the list or typed in, my problem is
when
the user selects the value from the list nothing happens, the code only
executes when typing and pressing enter.

Here's the code:

Private Sub Worksheet_Change(ByVal target As Range)
Application.ScreenUpdating = False
'Conditions & Ranges'
Dim rng As Range, rng2 As Range, rng3 As Range
Dim wf As WorksheetFunction
Dim cond1 As Boolean, cond2 As Boolean, cond3 As Boolean, cond4 As
Boolean

Set wf = Application.WorksheetFunction
Set rng = Me.Range("OU1") 'Option 1'
Set rng2 = Me.Range("OU2") 'Option 2'
Set rng3 = Me.Range("OU3") 'Option 3'
cond1 = (UCase(rng3.Value) = "YES")
cond2 = (UCase(rng3.Value) = "NO")
cond3 = (UCase(rng2.Value) = "YES")
cond4 = (UCase(rng2.Value) = "NO")

'Hides Packing Prices'
[38:38, 47:47].EntireRow.Hidden = wf.And(cond2, cond4)
[37:37, 46:46].EntireRow.Hidden = wf.Or(wf.And(cond1, cond3),
wf.And(cond2, cond4))

'Hides depending on Type'
[9:9].EntireRow.Hidden = (rng3.Value = "YES")
[10:21, 35:35, 44:44].EntireRow.Hidden = (rng3.Value = "NO")
'Hides Option 2'
If Not Intersect(rng, target) Is Nothing Then
[36:36, 45:45].EntireRow.Hidden = IsEmpty(rng.Value)
End If
Application.ScreenUpdating = True
End Sub