Function won't return value in merged cells

[Dr. Mike]

I have a need for when deleting the formula within a cell, another
cell's value will appear as default. Following is a formula that
serves, and works well in a single, unmerged cell. However, it--not
anything else I have tried--works in a group of merged cells. Does
anyone have a suggestion, and even better, an adaption to this formula?

Private Sub Worksheet_Change(ByVal TargetCell As Range)

If Not Intersect(TargetCell, Range("L14:L14")) Is Nothing Then
If TargetCell = "" Then
TargetCell = "=Sheet1!O14"
End If
End If
End Sub

Thanks,

Dr. M

 

[Don Guillett]

I don't use merged cells very often but try using all the merged cells such
as
range("L14:x14")

 

[Bob Phillips]

My simple test worked. What is happening with you?

--
HTH

Bob Phillips

(replace somewhere in email address with googlemail if mailing direct)

 

[Guest]

Is it a named range? I've had similar problems there. Try renaming it with
only the leading cell, if that doesn't work try "RangeStart:RangeStop"
hth Lou

 

[Dr. Mike]

The formula I posted works fine with single cells. However, what I am
trying to do is have the value of a single cell show within a single
selection of merged cells once I've deleted the contents of the merged
cells. What works for single cells doesn't seem to work for merged
cells. If you have a solution, I'm all ears. Regardless, thanks for
your effort.

Dr. M

 

[Dr. Mike]

Unfortunately, my project is limited in space and the text I'm trying
to bring to the merged cell also requires a text wrap. Anyway, thanks
for your response.

Dr. M