Change code HELP. Sentence Case

The below code changes the text in the designated range to sentence case,
however it ONLY does this for the first word.
How can i adapt it to do this to EACH word in the cell?

Sub SentenceCase()
Dim WS As Worksheet
Dim cell As Range

For Each WS In ThisWorkbook.worksheets
For Each cell In WS.Range("a8:A30,c7:r7")
s = cell.Value
Start = True
For i = 1 To Len(s)
ch = Mid(s, i, 1)
Select Case ch
Case "."
Start = True
Case "?"
Start = True
Case "a" To "z"
If Start Then ch = UCase(ch): Start = False
Case "A" To "Z"
If Start Then Start = False Else ch = LCase(ch)
End Select
Mid(s, i, 1) = ch
Next
cell.Value = s
Next
Next
End Sub

Regards
Corey....

Hi Corey,

Try:

'=============>>
Public Sub SentenceCase()
Dim WS As Worksheet
Dim rng As Range
Dim rCell As Range
Const sAddress = ("A8:A30,C7:R7")

For Each WS In ThisWorkbook.Worksheets
On Error Resume Next
Set rng = WS.Range(sAddress). _
SpecialCells(xlConstants, xlTextValues)
On Error GoTo 0
If Not rng Is Nothing Then
For Each rCell In rng.Cells
With rCell
.Value = Application.Proper(.Value)
End With
Next rCell
End If
Next WS
End Sub
'<<=============


---
Regards,
Norman

"Corey" <coreytroy@exemail.com.au> wrote in message
news:Oke2QgnvGHA.5088@TK2MSFTNGP06.phx.gbl...
The below code changes the text in the designated range to sentence case,
however it ONLY does this for the first word.
How can i adapt it to do this to EACH word in the cell?

Sub SentenceCase()
Dim WS As Worksheet
Dim cell As Range

For Each WS In ThisWorkbook.worksheets
For Each cell In WS.Range("a8:A30,c7:r7")
s = cell.Value
Start = True
For i = 1 To Len(s)
ch = Mid(s, i, 1)
Select Case ch
Case "."
Start = True
Case "?"
Start = True
Case "a" To "z"
If Start Then ch = UCase(ch): Start = False
Case "A" To "Z"
If Start Then Start = False Else ch = LCase(ch)
End Select
Mid(s, i, 1) = ch
Next
cell.Value = s
Next
Next
End Sub

Regards
Corey....

thanks Norman
Too easy

Corey....

"Norman Jones" <normanjones@whereforartthou.com> wrote in message
news:%23yn8trnvGHA.428@TK2MSFTNGP03.phx.gbl...
> Hi Corey,
>
> Try:
>
> '=============>>
> Public Sub SentenceCase()
> Dim WS As Worksheet
> Dim rng As Range
> Dim rCell As Range
> Const sAddress = ("A8:A30,C7:R7")
>
> For Each WS In ThisWorkbook.Worksheets
> On Error Resume Next
> Set rng = WS.Range(sAddress). _
> SpecialCells(xlConstants, xlTextValues)
> On Error GoTo 0
> If Not rng Is Nothing Then
> For Each rCell In rng.Cells
> With rCell
> .Value = Application.Proper(.Value)
> End With
> Next rCell
> End If
> Next WS
> End Sub
> '<<=============
>
>
> ---
> Regards,
> Norman
>
> "Corey" <coreytroy@exemail.com.au> wrote in message
> news:Oke2QgnvGHA.5088@TK2MSFTNGP06.phx.gbl...
> The below code changes the text in the designated range to sentence case,
> however it ONLY does this for the first word.
> How can i adapt it to do this to EACH word in the cell?
>
> Sub SentenceCase()
> Dim WS As Worksheet
> Dim cell As Range
>
> For Each WS In ThisWorkbook.worksheets
> For Each cell In WS.Range("a8:A30,c7:r7")
> s = cell.Value
> Start = True
> For i = 1 To Len(s)
> ch = Mid(s, i, 1)
> Select Case ch
> Case "."
> Start = True
> Case "?"
> Start = True
> Case "a" To "z"
> If Start Then ch = UCase(ch): Start = False
> Case "A" To "Z"
> If Start Then Start = False Else ch = LCase(ch)
> End Select
> Mid(s, i, 1) = ch
> Next
> cell.Value = s
> Next
> Next
> End Sub
>
> Regards
> Corey....
>