Excel 97/2003 VBA merged cells

[Tony James]

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

 

[Guest]

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

 

[Peter T]

Try -

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

Regards,
Peter T

 

[Tony James]

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