That seems to have done the job, thanks indeed !

Phil

"Simon Murphy" wrote:

> Phil
> I changed your logic a little and it seems ok. I also put an on error
> resume next to ignore any errors you do get. I think your code was erroring
> because you checked the value no matter which cell was the target - this
> version only does the work if they changed B4. Your version would error
> anytime there was an error value in the target cell, and probably other
> random times too as you need to use typed variables. Note too the 'for each'
> loop - its for each cell, I think yours may have been using the default
> (value) which would also cause the type mismatch.
>
> Private Sub Worksheet_Change(ByVal Target As Range)
> Dim c As Range
> On Error Resume Next
> If Target.Address = "$B$4" Then
> If Target.Value <> "" Then
> For Each c In Range("C5:R5").Cells
> If c.Value <> Target.Value Then
> c.EntireColumn.Hidden = True
> End If
> Next c
> ElseIf Target.Value = "" Then
> Columns("C:R").EntireColumn.Hidden = False
> 'Range("A4").Select
> End If
> Else
> 'changed cell is not B4 - do nothing
> End If
> End Sub
>
> cheers
> Simon
>
> "Phil Osman" wrote:
>
> > I have the following code in a sheet:
> >
> > Private Sub Worksheet_Change(ByVal Target As Range)
> > If Target.Address = "$B$4" And Target.Value <> "" Then
> > For Each c In Range("C5:R5")
> > If c.Value <> Target.Value Then
> > c.EntireColumn.Hidden = True
> > End If
> > Next
> > ElseIf Target.Address = "$B$4" And Target.Value = "" Then
> > Columns("C:R").Select
> > Selection.EntireColumn.Hidden = False
> > Range("A4").Select
> > End If
> > End Sub
> >
> > It hides all columns except the one referenced in a drop-down menu in Cell
> > B4. However, if I make changes anywhere else on the sheet it trys to run and
> > I get:
> > Run-time error '13':
> > Type mismatch
> >
> > All help appreciated !
> >
> > Phil