Find next cell

[Martin Wheeler]

XL 2000

The code below is supposed to colour yellow all cells in the range that fill
the criteria. But it is only working on the first applicable cell and not
going on to the next.
I have scavenged the code from another app and cannot see why it is not
repeating. Any help would be greatly appreciated.
Ta,
Martin
Public Sub FiftyKYellow(wks As Worksheet)
Application.ScreenUpdating = False
On Error Resume Next
With wks
If .Range("G1").Value > 50000 Then
For Each P In .Range("P731")
If P.Value < -0.195 Then
Set L = P
With L.Interior
.ColorIndex = 6
.Pattern = xlSolid
End With
End If
Exit For
Next
End If
Application.ScreenUpdating = True
End With
End Sub

 

[Steve Garman]

Try taking out the "Exit For"

 

[Frank Kabel]

Hi
delete the 'Exit for' statement

 

[Martin Wheeler]

Hi Frank,
Thanks - It works!
Ta,
Martin

Hi
delete the 'Exit for' statement

 

[Martin Wheeler]

Hi Steve,
Thanks - It works!
I'm always amazed when that happens
Ta,
Martin

 

[Trevor Shuttleworth]

Martin

the Exit For between the For... and the Next won't help.

Sub call50k()
FiftyKYellow ActiveSheet
End Sub

Public Sub FiftyKYellow(wks)
Dim P As Range
Application.ScreenUpdating = False
On Error Resume Next
With wks
If .Range("G1").Value > 50000 Then
For Each P In .Range("P731")
If P.Value < -0.195 Then
With P.Interior
.ColorIndex = 6
.Pattern = xlSolid
End With
End If
Next
End If
Application.ScreenUpdating = True
End With
End Sub


Regards

Trevor