Hi Peter
I tried it and it works great. Now I will keep this one and study it for a
while, it's way pass my knowledge.
Great shot!!
Regards
John
"Peter T" <peter_t@discussions wrote in message
...
Have a go with something like the following

' code in a normal module
Private mRngLast As Range

Sub NextRefError()
Dim bGetNext As Boolean, bCompare As Boolean, bGotOld As Boolean
Dim i As Long, j As Long
Dim idx As Long
Dim firstAddress As String, sLast As String
Dim c As Range, rNextErr As Range
Dim ws As Worksheet

For i = 1 To 2
On Error Resume Next
idx = 0
idx = mRngLast.Parent.Index
On Error GoTo 0
If idx 0 Then
If Not mRngLast.Parent.Parent Is ActiveWorkbook Then
If MsgBox("Do you want to switch to " & _
mRngLast.Parent.Parent.Name & _
vbCr & "Or press No to reset, then try again", _
vbYesNo) = vbYes Then
mRngLast.Parent.Activate
Else
Set mRngLast = Nothing
Exit Sub
End If
End If

bGotOld = True
bCompare = True

sLast = mRngLast.Address
Else
bCompare = False
idx = 1
End If

For j = idx To ActiveWorkbook.Worksheets.Count
With ActiveWorkbook.Worksheets(j)

Set c = .Cells.Find(What:="#REF!", After:=.Range("A1"), _
LookIn:=xlValues, LookAt:=xlPart, _
SearchOrder:=xlByRows, SearchDirection:=xlNext)
If Not c Is Nothing Then

If bCompare = False Then
Set rNextErr = c
Exit For
ElseIf c.Address = sLast Then
bGetNext = True
End If
firstAddress = c.Address

Do
Set c = .Cells.FindNext(c)
If bGetNext Then
If c.Address < firstAddress Then
Set rNextErr = c
End If
Exit Do
ElseIf bCompare Then
If c.Address = sLast Then
bGetNext = True
End If
End If
Loop While Not c Is Nothing And c.Address < firstAddress
End If
End With

If Not rNextErr Is Nothing Then Exit For
bCompare = False
bGetNext = False
Next

If Not rNextErr Is Nothing Then
Set mRngLast = rNextErr
mRngLast.Parent.Activate
mRngLast.Select
' mRngLast.Interior.ColorIndex = 3

Exit For
ElseIf bGotOld = False Then
MsgBox "#REF! not found"
Exit For
Else
If MsgBox("Reset and search from beginning?", vbYesNo) _
< vbYes Then
Exit For
Else
idx = 0
Set mRngLast = Nothing
End If
End If
Next

End Sub

Regards,
Peter T


"Jan Kronsell" wrote in message
...
I have this code:

Sub t()
For Each s In ActiveWorkbook.Sheets
For Each c In s.UsedRange.Cells
If IsError(c) Then
errval = c.Value
If errval = CVErr(xlErrRef) Then
c.Interior.ColorIndex = 3
End If
End If
Next c
Next s
End Sub

It colors all cells red, if the contains the #REF! error. The problem is,
that my client would like it to select the first instans of the error and
stop. When he runs it a second time, it should stop with the second instans
and so on.

I don't see how this can be done?

Jan