Glad to help.

To explain it a bit mo

If Not Application.Intersect(Target, Range("D4")) Is Nothing Then

The line above makes sure that the changed-cell intersects with D4 before
evaluating anything.

The other issue was when clearing D4, and since D4 was merged with E4, it
resulted in a change to 2 cells. Investigate it and you'll see the issue:

Here is the code with a Stop. Enter a value, then clear it and it will stop
at the stop point. Step through and debug.print the address.

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Target, Range("D4")) Is Nothing Then

If Target.Cells.Count 1 Then Stop '< -- when clearing D4 it
will stop here.
Debug.Print Target.Address '< -- results in
$D$4:$E$4 because it is merged.

If Target.Cells(1, 1).Value = "SMART Cable" Then '< -- top left
cell, for single or multi cells.
Sheets("Load v Distance").Visible = True
Sheets("Load v Time").Visible = True
Else
Sheets("Load v Distance").Visible = False
Sheets("Load v Time").Visible = False
End If
End If
End Sub

"Target.Cells(1,1)" returns the top-left cell of a single or muti-cell
range. Normally, Target is only a single cell. But when it is part of a
merged area, it sometimes evaluates as more than one cell -- like when
clearing D4. Cells(1,1) returns the top left cell.

When using merged cells, workaround code such as this is needed. But the
code is safe enough to continue to work even if you decide to un-merge
D4:E4.


--
Regards,
Tim Zych
http://www.higherdata.com
Workbook Compare - Excel data comparison utility


"Al" wrote in message
...
Tim,

that works a treat, thanks very much. Can't quite understand how but it
works. Cheers.

"Tim Zych" wrote:

This should help:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Target, Range("D4")) Is Nothing Then
If Target.Cells(1, 1).Value = "SMART Cable" Then
Sheets("Load v Distance").Visible = True
Sheets("Load v Time").Visible = True
Else
Sheets("Load v Distance").Visible = False
Sheets("Load v Time").Visible = False
End If
End If
End Sub

--
Regards,
Tim Zych
http://www.higherdata.com
Workbook Compare - Excel data comparison utility

"Al" wrote in message
...
I have some code running in a worksheet (sheet 1) to hide two worksheets
unless the value (text) in a cell (D4:E4 merged) of 'sheet 1' is a
certain
value. The value in 'D4:E4' is chosen from a list of 4 options (set up
using
a data validationlist).

I am getting a type mismatch error if the value in 'd4' is
deleted/cleared
(not much of an issue as should only select form the list). More of an
issue
is I get the same error when any other merged cells (i.e. "C2:I2",
"B18:J20")
are cleared (i.e select cell and hit 'Delete').

Any ideas on why the error occurs or how to avoid it?

The code:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target = Range("D4") Then
If Target.Value = "SMART Cable" Then
Sheets("Load v Distance").Visible = True
Sheets("Load v Time").Visible = True
Else
Sheets("Load v Distance").Visible = False
Sheets("Load v Time").Visible = False
End If
End If
End Sub