Using vb to Correctly format sentances

[flebber]

I am looking at the formula/macro and want a little help with how I could adjust it to add in a Range that I have as a defined name. In my case I defined the range as "comments".

I am drawing the basis of the formula from this thread http://www.excelforum.com/excel-gene...ence-case.html
Which uses this as the solution to Correctly formatting sentences in cells. I want to use it with defined ranges.

Sub AAA()
Dim Rng As Range
For Each Rng In ActiveSheet.UsedRange.SpecialCells( _
xlCellTypeConstants, xlTextValues)
Rng.Value = UCase(Left(Rng.Text, 1)) & Mid(Rng.Text, 2)
Next Rng
End Sub

Tried changing it to because my activesheet is called ServiceDeliveryTemplate and the defined name is Comments but I am not selecting the range correctly.

Sub AAA()
Dim Rng As Range
For Each Rng In ServiceDeliveryTemplate.Comments.SpecialCells(xlCellTypeConstants, xlTextValues)
Rng.Value = UCase(Left(Rng.Text, 1)) & Mid(Rng.Text, 2)
Next Rng
End Sub