Private Sub Worksheet_Change(ByVal Target As Range)
Dim r As Long

On Error Goto ws_exit
Application.EnableEvents = False

r = 9

Do Until IsEmpty(Cells(r, 12))

If Cells(r, 12) = "Yes" Then
Cells(r, 13) = 1

ElseIf Cells(r, 12) = "No" Then
Cells(r, 13) = 2
End If
r = r + 1
Loop
ws_exit:
Application.EnableEvents = True
End Sub


--

HTH

RP
(remove nothere from the email address if mailing direct)


"pjjclark" wrote in
message ...

New to this world, so please feel free to patronise!

I am trying to asign a value of either 1 or 2 depending of the
condition of Yes or No, in two colums next to each other.

This is so that I can add up the values and determine whether I have
all yes's.

This is what I have written so far:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim r As Long

r = 9

Do Until IsEmpty(Cells(r, 12))

If Cells(r, 12) = "Yes" Then
Cells(r, 13) = 1

ElseIf Cells(r, 12) = "No" Then
Cells(r, 13) = 2
End If
r = r + 1
Loop
End Sub


The issue is that its fine for the first row, slow for the second, and
then locks up for the third.

There is something fundemental that I am missing, if you could point me
in the right direction, I would appreciate it.

Thanks.


--
pjjclark
------------------------------------------------------------------------
pjjclark's Profile:
http://www.excelforum.com/member.php...o&userid=29180
View this thread: http://www.excelforum.com/showthread...hreadid=489087