Hello

I have a problem which appears to be to do with the different ways in
which Excel 97 and 2003 treat merged cells.


In an Excel 2003 worksheet cell G5 is merged from 4 cells (G5:J5)

The following Excel 2003 VBA code works well ("OPS" is a named range
for cell G5):

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

If Target.Cells.Count = 1 Then 'do nothing if more than one cell is
changed

If Target.Address = Range("OPS").Address Then
'do stuff

'rest of code cut

End If

End If

End Sub


The code fails in Excel 97. Stepping through the code reveals that
Target.Cells.Count is equal to 4 and likewise Target.Address is equal
to "$G$5:$J$5".

Is there anything I can do about this other than to check which version
of Excel is running? Any other coding suggestions welcome.
Thanks

Maybe something like this untested pseudo code. You will need to test an
approach like this to see what works the way you want.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)


If Target.Cells.Count < 1 Then 'do nothing if more than one cell is
if Target(1).MergeArea.Address < Target.Address then
exit sub
else
set rng = Target(1)
end if
Else
set rng = Target
End if

If rng.Address = Range("OPS").Address Then
'do stuff

'rest of code cut

End If

End If

--
Regards,
Tom Ogilvy


"Tony James" wrote:

Hello

I have a problem which appears to be to do with the different ways in
which Excel 97 and 2003 treat merged cells.


In an Excel 2003 worksheet cell G5 is merged from 4 cells (G5:J5)

The following Excel 2003 VBA code works well ("OPS" is a named range
for cell G5):

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

If Target.Cells.Count = 1 Then 'do nothing if more than one cell is
changed

If Target.Address = Range("OPS").Address Then
'do stuff

'rest of code cut

End If

End If

End Sub


The code fails in Excel 97. Stepping through the code reveals that
Target.Cells.Count is equal to 4 and likewise Target.Address is equal
to "$G$5:$J$5".

Is there anything I can do about this other than to check which version
of Excel is running? Any other coding suggestions welcome.
Thanks

Try -

If Target.Count = Target(1).MergeArea.Count Then

Regards,
Peter T

"Tony James" wrote in message
ups.com...
Hello

I have a problem which appears to be to do with the different ways in
which Excel 97 and 2003 treat merged cells.


In an Excel 2003 worksheet cell G5 is merged from 4 cells (G5:J5)

The following Excel 2003 VBA code works well ("OPS" is a named range
for cell G5):

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

If Target.Cells.Count = 1 Then 'do nothing if more than one cell is
changed

If Target.Address = Range("OPS").Address Then
'do stuff

'rest of code cut

End If

End If

End Sub


The code fails in Excel 97. Stepping through the code reveals that
Target.Cells.Count is equal to 4 and likewise Target.Address is equal
to "$G$5:$J$5".

Is there anything I can do about this other than to check which version
of Excel is running? Any other coding suggestions welcome.
Thanks

Tom and Peter, many thanks for your suggestions. I'll try your code
when I return to work tomorrow, they both look better than coding for
different Excel versions.

Thanks
Tony