You got a couple responses yesterday:
http://groups.google.com/groups?thre...5FF6%40msn.com

(Are you punchy?)

raj wrote:

Hello. Please help me if you can.

I'm trying to set up an on sheet change, which I can get
to fire, but not successfully.

My code is supposed to reformat a range of cells from this:

123123123412345612312123

to this:

123.123.1234.123456.123.12.123

It must also ensure that the pattern is entered correctly,
i.e. not text or a partial set of values, etc.

Here is my code in a sheet module (you should be able to
copy directly to a module):

Private Sub Worksheet_Calculate()

Dim rngCell As Range

For Each rngCell In Range("A24:A30").Rows
If Not rngCell.FormulaR1C1 = "" And Not _
rngCell.FormulaR1C1
Like "###.###.####.######.###.##.###" Then
rngCell.Activate
MsgBox "Invalid account distribution!",
vbCritical, _
"INVALID ACCOUNT DISTRIBUTION!": Exit Sub
End If
Next rngCell

End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As
Range)

Dim rngCell As Range
Dim strAccount As String

For Each rngCell In Range("A24:A30").Rows
If rngCell.FormulaR1C1 Like "########################"
Then
strAccount = rngCell.FormulaR1C1
strAccount = Left(strAccount, 3) & "." & _
Mid(strAccount, 4, 3) & "." & _
Mid(strAccount, 7, 4) & "." & _
Mid(strAccount, 11, 6) & "." & _
Mid(strAccount, 17, 3) & "." & _
Mid(strAccount, 20, 2) & "." & _
Right(strAccount, 3)
rngCell.FormulaR1C1 = strAccount
End If
Next rngCell

End Sub

Your example code would be most helpful. Thanks in advance.

--

Dave Peterson