All credit to JLatham for the code below - A BIG THANKYOU! I now have
a UserForm with two text boxes, a "Search" Button and "Cancel"
Button. It works perfectly for one occurrence, but can someone please
help me by adding what I need in order to find the NEXT occurence each
time the user clicks the "Search" button? (I am searching for the
contents of a cell within a range that is NOT red & strikethrough). I
would soooooo appreciate the help! Thanks in advance. Here's the
code:

Const whatColor = 3 ' 3=red
Dim searchList As Range
Dim anyEntry As Range
Dim findEntry As String

'get the surname entry
'remove leading/trailing whitespace
'and convert to UPPERCASE for tests
findEntry = UCase(Trim(Me.TextBox1))
If findEntry < "" Then
'we do have a last name to seek
'look in column B for it
Set searchList = ActiveSheet.Range("B1:" & _
ActiveSheet.Range("B" & Rows.Count).End(xlUp). _
Offset(1, 0).Address)
For Each anyEntry In searchList
If UCase(Trim(anyEntry)) = findEntry Then
'name matched, check formatting
If anyEntry.Font.ColorIndex < whatColor And _
anyEntry.Font.Strikethrough = False Then
anyEntry.Activate
Set searchList = Nothing
Exit Sub ' all finished
End If
End If
Next
'if we get here, no match on lastname
'was found, see if we have a first name
'to look for
findEntry = UCase(Trim(Me.TextBox1))
If findEntry < "" Then
'have a first name to look for
'look in column C for it
Set searchList = ActiveSheet.Range("C1:" & _
ActiveSheet.Range("C" & Rows.Count).End(xlUp). _
Offset(1, 0).Address)
For Each anyEntry In searchList
If UCase(Trim(anyEntry)) = findEntry Then
'name matched, check formatting
If anyEntry.Font.ColorIndex < whatColor And _
anyEntry.Font.Strikethrough = False Then
anyEntry.Activate
Set searchList = Nothing
Exit Sub ' all finished
End If
End If
Next
End If
Else
'did not have last name, do we have
'a first name to search for
findEntry = UCase(Trim(Me.TextBox2))
If findEntry < "" Then
'have a first name to look for
'look in column C for it
Set searchList = ActiveSheet.Range("C1:" & _
ActiveSheet.Range("C" & Rows.Count).End(xlUp). _
Offset(1, 0).Address)
For Each anyEntry In searchList
If UCase(Trim(anyEntry)) = findEntry Then
'name matched, check formatting
If anyEntry.Font.ColorIndex < whatColor And _
anyEntry.Font.Strikethrough = False Then
anyEntry.Activate
Set searchList = Nothing
Exit Sub ' all finished
End If
End If
Next
End If
End If

Without looking at this, have you tried FINDNEXT. Look in the vba help index


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Jeanette" wrote in message
...
All credit to JLatham for the code below - A BIG THANKYOU! I now have
a UserForm with two text boxes, a "Search" Button and "Cancel"
Button. It works perfectly for one occurrence, but can someone please
help me by adding what I need in order to find the NEXT occurence each
time the user clicks the "Search" button? (I am searching for the
contents of a cell within a range that is NOT red & strikethrough). I
would soooooo appreciate the help! Thanks in advance. Here's the
code:

Const whatColor = 3 ' 3=red
Dim searchList As Range
Dim anyEntry As Range
Dim findEntry As String

'get the surname entry
'remove leading/trailing whitespace
'and convert to UPPERCASE for tests
findEntry = UCase(Trim(Me.TextBox1))
If findEntry < "" Then
'we do have a last name to seek
'look in column B for it
Set searchList = ActiveSheet.Range("B1:" & _
ActiveSheet.Range("B" & Rows.Count).End(xlUp). _
Offset(1, 0).Address)
For Each anyEntry In searchList
If UCase(Trim(anyEntry)) = findEntry Then
'name matched, check formatting
If anyEntry.Font.ColorIndex < whatColor And _
anyEntry.Font.Strikethrough = False Then
anyEntry.Activate
Set searchList = Nothing
Exit Sub ' all finished
End If
End If
Next
'if we get here, no match on lastname
'was found, see if we have a first name
'to look for
findEntry = UCase(Trim(Me.TextBox1))
If findEntry < "" Then
'have a first name to look for
'look in column C for it
Set searchList = ActiveSheet.Range("C1:" & _
ActiveSheet.Range("C" & Rows.Count).End(xlUp). _
Offset(1, 0).Address)
For Each anyEntry In searchList
If UCase(Trim(anyEntry)) = findEntry Then
'name matched, check formatting
If anyEntry.Font.ColorIndex < whatColor And _
anyEntry.Font.Strikethrough = False Then
anyEntry.Activate
Set searchList = Nothing
Exit Sub ' all finished
End If
End If
Next
End If
Else
'did not have last name, do we have
'a first name to search for
findEntry = UCase(Trim(Me.TextBox2))
If findEntry < "" Then
'have a first name to look for
'look in column C for it
Set searchList = ActiveSheet.Range("C1:" & _
ActiveSheet.Range("C" & Rows.Count).End(xlUp). _
Offset(1, 0).Address)
For Each anyEntry In searchList
If UCase(Trim(anyEntry)) = findEntry Then
'name matched, check formatting
If anyEntry.Font.ColorIndex < whatColor And _
anyEntry.Font.Strikethrough = False Then
anyEntry.Activate
Set searchList = Nothing
Exit Sub ' all finished
End If
End If
Next
End If
End If

On 17 June, 23:08, "Don Guillett" wrote:
Without looking at this, have you tried FINDNEXT. Look in the vba help index

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
"Jeanette" wrote in message

...



All credit to JLatham for the code below - A BIG THANKYOU! *I now have
a UserForm with two text boxes, a "Search" Button and "Cancel"
Button. *It works perfectly for one occurrence, but can someone please
help me by adding what I need in order to find the NEXT occurence each
time the user clicks the "Search" button? (I am searching for the
contents of a cell within a range that is NOT red & strikethrough). *I
would soooooo appreciate the help! *Thanks in advance. *Here's the
code:

*Const whatColor = 3 ' 3=red
*Dim searchList As Range
*Dim anyEntry As Range
*Dim findEntry As String

*'get the surname entry
*'remove leading/trailing whitespace
*'and convert to UPPERCASE for tests
*findEntry = UCase(Trim(Me.TextBox1))
*If findEntry < "" Then
* *'we do have a last name to seek
* *'look in column B for it
* *Set searchList = ActiveSheet.Range("B1:" & _
* * ActiveSheet.Range("B" & Rows.Count).End(xlUp). _
* * Offset(1, 0).Address)
* *For Each anyEntry In searchList
* * *If UCase(Trim(anyEntry)) = findEntry Then
* * * *'name matched, check formatting
* * * *If anyEntry.Font.ColorIndex < whatColor And _
* * * * anyEntry.Font.Strikethrough = False Then
* * * * *anyEntry.Activate
* * * * *Set searchList = Nothing
* * * * *Exit Sub ' all finished
* * * *End If
* * *End If
* *Next
* *'if we get here, no match on lastname
* *'was found, see if we have a first name
* *'to look for
* *findEntry = UCase(Trim(Me.TextBox1))
* *If findEntry < "" Then
* * *'have a first name to look for
* * *'look in column C for it
* * *Set searchList = ActiveSheet.Range("C1:" & _
* * * ActiveSheet.Range("C" & Rows.Count).End(xlUp). _
* * * Offset(1, 0).Address)
* * *For Each anyEntry In searchList
* * * *If UCase(Trim(anyEntry)) = findEntry Then
* * * * *'name matched, check formatting
* * * * *If anyEntry.Font.ColorIndex < whatColor And _
* * * * * anyEntry.Font.Strikethrough = False Then
* * * * * *anyEntry.Activate
* * * * * *Set searchList = Nothing
* * * * * *Exit Sub ' all finished
* * * * *End If
* * * *End If
* * *Next
* *End If
*Else
* *'did not have last name, do we have
* *'a first name to search for
* *findEntry = UCase(Trim(Me.TextBox2))
* *If findEntry < "" Then
* * *'have a first name to look for
* * *'look in column C for it
* * *Set searchList = ActiveSheet.Range("C1:" & _
* * * ActiveSheet.Range("C" & Rows.Count).End(xlUp). _
* * * Offset(1, 0).Address)
* * *For Each anyEntry In searchList
* * * *If UCase(Trim(anyEntry)) = findEntry Then
* * * * *'name matched, check formatting
* * * * *If anyEntry.Font.ColorIndex < whatColor And _
* * * * * anyEntry.Font.Strikethrough = False Then
* * * * * *anyEntry.Activate
* * * * * *Set searchList = Nothing
* * * * * *Exit Sub ' all finished
* * * * *End If
* * * *End If
* * *Next
* *End If
*End If- Hide quoted text -

- Show quoted text -

Thanks Don, yes I have looked at FINDNEXT in the vba help, but I don't
know where or how to tie it into the code I have!!! Perhaps someone
can help?