Stone,

That is because you are iterating through the range using a range object,
named rngCell, but then you try and reference it via Selection, which is not
where rngCell is pointing to, and could be anywhere.

Try this

Dim rngCell As Range
For Each rngCell In ActiveSheet.UsedRange
If rngCell.MergeCells = True Then
rngCell.UnMerge
rngCell.HorizontalAlignment = xlCenterAcrossSelection
End If
Next rngCell


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"stone" wrote in message
...
I need a function that would loop through all the cells in the used range
of a single sheet and convert any merged cells to "xlCenterAcrossSelection"
for the same range as the cells that were originally merged.

For example, if the range "B2:J2" was merged, convert range "B2:J2" to
centered across the selection.

My code (below) unmerges the cells but then fails to convert them to
"center across":

Dim rngCell As Range
For Each rngCell In ActiveSheet.UsedRange
If rngCell.MergeCells = True Then
Selection.UnMerge
Selection.HorizontalAlignment = xlCenterAcrossSelection <this
line fails without any errors
End If
Next rngCell

Could someone please correct my code and/or show me example code that
would accomplish what I'm trying to do?

TIA