Another Option


Sub WorksheetC()
Dim r As Long
Dim cellVal As Boolean

r = 1
cellVal = True

Do Until IsEmpty(Cells(r, 1)) Or cellVal = False
Cells(r, 1).Select
cellVal = (Cells(r, 1) = "Yes")
r = r + 1
Loop
If cellVal = False Then MsgBox "Not all Yes!"
End Sub

Brian



"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