Find and Replace

[LePig]

Hi,

I am trying to look through some cells and replace "," with ".".

I need this to happen on worksheet change event.

I only want it to be fired though when specific cells have been changed.

Please see code below.

Private Sub Worksheet_Change(ByVal Target As Range)

Dim KeyCells As Range

Set KeyCells = Sheets("Sheet1").Range("A1:A10")
           If Not Application.Intersect(KeyCells, Range(Target.Address)) _
           Is Nothing Then

           For Each MyCell In Range("A1:A10")

           MyCell.Value = Replace(MyCell.Value, ",", ".")

           Next MyCell

           Else

           End If

End Sub

I am getting Error Code -2147417848(80010108) Method Range of Object Worksheet Failed

If i put this code on a button click it works fine. It appears to be when i try to restrict the cells. Any help would be much appreciated.

For Each MyCell In Range("A1:A10")

MyCell.Value = Replace(MyCell.Value, ",", ".")

Next MyCell

Kind Regards