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.