For some reason, the code has changed itself back as below.

Private Sub Worksheet_Calculate()
Set c = Range("M7")
For Each i In c
If i.Value = "Yes" Then
i.Value = i.Value
End If
Next
End Sub

As soon as I try to change the range back to ("M7:M20") as I need it, Excel
freezes.
Does anyone know how I may overcome this.

--
Cheers
cliff18


"cliff18" wrote:

Sorry Max, but I found when I shut the application down and restart it I get
a 'Run-time error '13';' I can simply end it and it carries on so it's not a
big problem I guess.
I'll spend more time with it later.
Thanks again mate!
--
Cheers
cliff18


"cliff18" wrote:

If I could tinker like that I'd spend less time here, that's for sure!
After what you've given me, I simply changed the range in the macro to
("M7:M20") for reloading the formula and it all seems perfect!
--
Thanks Max, I had no idea.
cliff18


"Max" wrote:

Tinkered around, this seems ok:

Private Sub Worksheet_Calculate()
Set c = Range("M7:M20")
For Each i In c
If i.Value = "Yes" Then
i.Value = i.Value
End If
Next
End Sub

--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,000 Files:370 Subscribers:66
xdemechanik
---
"cliff18" wrote:
Thanks Max, what you gave me worked perfectly, including reloading the
formula with the Macro!
The only thing I didn't mention is that I would like this formula to work
all the way down from row 7 (as in the formula I sent) down to row 20. I
thought I could work it out myself with what you gave me, but unfortunatley I
am just getting errors as I change the range as below.
'------
Private Sub Worksheet_Calculate()
If Range("M7:M20").Value = "Yes" Then
Range("M7:M20").Value = Range("M7:M20").Value
End If
End Sub
'-----
Sorry to ask again but is it possible to get this to work in this way.