Excel Conditional Find and Go to cell

[Jeanette]

I'd really appreciate some help...
I'd like to create a userform with two input boxes - one for Surname,
one for Firstname, two CommandButtons, one to perform action, the
other to cancel.
The action I want to perform is for the user to type a surname in
inputbox1 and then do a search in column B for that surname, that's
NOT red/strikethrough OR for the user to type a Firstname in inputbox2
and do a search for that firstname in column C that's NOT red/
strikethrough. If one of you brilliant techies can help me, I'd be
ever grateful!!

 

[JLatham]

Jeanette,
I don't know how much you know/don't know about user forms, so I'll go
through this in some detail and you can skip over the parts you don't need.
If there are parts you need more help with, just ask.

First, you'll need a way to open your userform once you get it created. For
that we need a regular subroutine. Open your workbook and press [Alt]+[F11]
to open the VB editor. In its menu, choose Insert --> Module and copy and
paste this code into it:
Sub OpenUserForm1()
UserForm1.Show
End Sub

Next, still in the VB editor, choose Insert --> UserForm it should insert a
form named UserForm1 for you. Use the toolbox to add a label and change the
text in it to something like "Surname" or "Last Name". Add a textbox next to
it, that text box's name will be TextBox1. Repeat this process to add
another label ("First Name" in it) and another text box that should be
TextBox2.

We're ready for the 2 command buttons: put one on the form and set its text
to something like "Search", and set the second one's text to something like
"Cancel/Close".

Right-click in a clear area anywhere on the userform and choose [View Code]
from the list. A module will open up with a 'stub' entry for
UserForm_Click() created. You don't need that. Just copy all the code below
and paste it over anything that the editor provided for you. Your userform
will be ready to use then. To try it all out, close the VB Editor and then
from the Excel worksheet with your list of names on it, use Tools --> Macro
--> Macros to highlight the OpenUserForm macro and use the [Run] button to
open it up for use. Here's the code for the userform:


Private Sub CommandButton1_Click()
'the "search" button
'will look for last name entries first
'if match is found, stops completely
'if no match found, checks to see
'if there's an entry in first name box
'and if there is, will search for match
'
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
End Sub

Private Sub CommandButton2_Click()
'the "cancel" button
Unload Me
End Sub

Private Sub UserForm_Initialize()
Me.TextBox1 = "" ' last name box
Me.TextBox2 = "" ' first name box
End Sub

 

[Jeanette]

Jeanette,
I don't know how much you know/don't know about user forms, so I'llgo
through this in some detail and you can skip over the parts you don't need.  
If there are parts you need more help with, just ask.

First, you'll need a way to open your userform once you get it created.  For
that we need a regular subroutine.  Open your workbook and press [Alt]+[F11]
to open the VB editor.  In its menu, choose Insert --> Module and copy and
paste this code into it:
Sub OpenUserForm1()
  UserForm1.Show
End Sub

Next, still in the VB editor, choose Insert --> UserForm  it should insert a
form named UserForm1 for you.  Use the toolbox to add a label and change the
text in it to something like "Surname" or "Last Name".  Add a textbox next to
it, that text box's name will be TextBox1.  Repeat this process to add
another label ("First Name" in it) and another text box that should be
TextBox2.

We're ready for the 2 command buttons:  put one on the form and set itstext
to something like "Search", and set the second one's text to something like
"Cancel/Close".

Right-click in a clear area anywhere on the userform and choose [View Code]
from the list.  A module will open up with a 'stub' entry for
UserForm_Click() created.  You don't need that.  Just copy all the code below
and paste it over anything that the editor provided for you.  Your userform
will be ready to use then.  To try it all out, close the VB Editor and then
from the Excel worksheet with your list of names on it, use Tools --> Macro
--> Macros to highlight the OpenUserForm macro and use the [Run] button to
open it up for use.  Here's the code for the userform:

Private Sub CommandButton1_Click()
  'the "search" button
  'will look for last name entries first
  'if match is found, stops completely
  'if no match found, checks to see
  'if there's an entry in first name box
  'and if there is, will search for match
  '
  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
End Sub

Private Sub CommandButton2_Click()
  'the "cancel" button
  Unload Me
End Sub

Private Sub UserForm_Initialize()
  Me.TextBox1 = "" ' last name box
  Me.TextBox2 = "" ' first name box
End Sub

I'd really appreciate some help...
I'd like to create a userform with two input boxes - one for Surname,
one for Firstname, two CommandButtons, one to perform action, the
other to cancel.
The action I want to perform is for the user to type a surname in
inputbox1 and then do a search in column B for that surname, that's
NOT red/strikethrough OR for the user to type a Firstname in inputbox2
and do a search for that firstname in column C that's NOT red/
strikethrough.  If one of you brilliant techies can help me, I'd be
ever grateful!!- Hide quoted text -

- Show quoted text -

JLatham very kindly helped me out with this and I have replied in the
Group, but no-one is able to help me so I'm posting this again. I’m
hoping that someone could tweak this code so that I can keep
performing the same task until I find the entry I want (you see, some
people may have the same surname or the same firstname) – i.e., search
again but from the active cell/current position. Here’s how I need
for it to work:

1. Find some surname in column B that’s not red strikethrough
2. Go to that cell
3. (Found the surname but that’s not the one I want because the
firstname in column C isn’t the right match)
4. Find some surname again in column B that’s not red strikethrough
(but this time, search from the one I just found)
5. Go to that cell
6. Found the surname but that’s not the one I want either .... etc.,
etc. (keep repeating Nos. 4 and 5 with each click of the
CommandButton)

I did create the userform as instructed, and I’ve even managed to add
another CommandButton(3) that shows itself after the code has run. So
the ideal scenario would be to perform the repeated searches (Nos. 4
and 5 above) when you click that button. God, I hope this makes
sense! I know what I want to do, but haven’t got a clue how to get
there! So if anyone can help me, you’d be a star!

Thanks in anticipation.
Jeanette

 

[JLatham]

Sorry to take so long, got tied up on some other stuff. Anyhow...

Replace the code I gave you before with the code before. You won't need
your 3rd button. Instead, when a match is found a prompt will come up asking
whether or not to continue searching. You simply respond by clicking the
[Yes] or [No] button in the prompt window.

Private Sub CommandButton1_Click()
'the "search" button
'will look for last name entries first
'if match is found, stops completely
'if no match found, checks to see
'if there's an entry in first name box
'and if there is, will search for match
'
Const whatColor = 3 ' 3=red
Dim searchList As Range
Dim anyEntry As Range
Dim findEntry As String
Dim whatToDoNow As Integer

'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
Application.Goto anyEntry, True
anyEntry.Activate
'stop or continue searching for last name
If MsgBox("Continue searching?", vbYesNo, "What Now?") <> vbYes Then
Set searchList = Nothing
Exit Sub
End If
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
Application.Goto anyEntry, True
anyEntry.Activate
'stop or continue searching for last name
If MsgBox("Continue searching?", vbYesNo, "What Now?") <> vbYes
Then
Set searchList = Nothing
Exit Sub
End If
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
Application.Goto anyEntry, True
anyEntry.Activate
'stop or continue searching for last name
If MsgBox("Continue searching?", vbYesNo, "What Now?") <> vbYes
Then
Set searchList = Nothing
Exit Sub
End If
End If
End If
Next
End If
End If
End Sub

Private Sub CommandButton2_Click()
'the "cancel" button
Unload Me
End Sub

Private Sub UserForm_Initialize()
Me.TextBox1 = "" ' last name box
Me.TextBox2 = "" ' first name box
End Sub

Jeanette,
I don't know how much you know/don't know about user forms, so I'llgo
through this in some detail and you can skip over the parts you don't need.
If there are parts you need more help with, just ask.

First, you'll need a way to open your userform once you get it created. For
that we need a regular subroutine. Open your workbook and press [Alt]+[F11]
to open the VB editor. In its menu, choose Insert --> Module and copy and
paste this code into it:
Sub OpenUserForm1()
UserForm1.Show
End Sub

Next, still in the VB editor, choose Insert --> UserForm it should insert a
form named UserForm1 for you. Use the toolbox to add a label and change the
text in it to something like "Surname" or "Last Name". Add a textbox next to
it, that text box's name will be TextBox1. Repeat this process to add
another label ("First Name" in it) and another text box that should be
TextBox2.

We're ready for the 2 command buttons: put one on the form and set its text
to something like "Search", and set the second one's text to something like
"Cancel/Close".

Right-click in a clear area anywhere on the userform and choose [View Code]
from the list. A module will open up with a 'stub' entry for
UserForm_Click() created. You don't need that. Just copy all the code below
and paste it over anything that the editor provided for you. Your userform
will be ready to use then. To try it all out, close the VB Editor and then
from the Excel worksheet with your list of names on it, use Tools --> Macro
--> Macros to highlight the OpenUserForm macro and use the [Run] button to
open it up for use. Here's the code for the userform:

Private Sub CommandButton1_Click()
'the "search" button
'will look for last name entries first
'if match is found, stops completely
'if no match found, checks to see
'if there's an entry in first name box
'and if there is, will search for match
'
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
End Sub

Private Sub CommandButton2_Click()
'the "cancel" button
Unload Me
End Sub

Private Sub UserForm_Initialize()
Me.TextBox1 = "" ' last name box
Me.TextBox2 = "" ' first name box
End Sub

I'd really appreciate some help...
I'd like to create a userform with two input boxes - one for Surname,
one for Firstname, two CommandButtons, one to perform action, the
other to cancel.
The action I want to perform is for the user to type a surname in
inputbox1 and then do a search in column B for that surname, that's
NOT red/strikethrough OR for the user to type a Firstname in inputbox2
and do a search for that firstname in column C that's NOT red/
strikethrough. If one of you brilliant techies can help me, I'd be
ever grateful!!- Hide quoted text -

- Show quoted text -

JLatham very kindly helped me out with this and I have replied in the
Group, but no-one is able to help me so I'm posting this again. I’m
hoping that someone could tweak this code so that I can keep
performing the same task until I find the entry I want (you see, some
people may have the same surname or the same firstname) – i.e., search
again but from the active cell/current position. Here’s how I need
for it to work:

1. Find some surname in column B that’s not red strikethrough
2. Go to that cell
3. (Found the surname but that’s not the one I want because the
firstname in column C isn’t the right match)
4. Find some surname again in column B that’s not red strikethrough
(but this time, search from the one I just found)
5. Go to that cell
6. Found the surname but that’s not the one I want either .... etc.,
etc. (keep repeating Nos. 4 and 5 with each click of the
CommandButton)

I did create the userform as instructed, and I’ve even managed to add
another CommandButton(3) that shows itself after the code has run. So
the ideal scenario would be to perform the repeated searches (Nos. 4
and 5 above) when you click that button. God, I hope this makes
sense! I know what I want to do, but haven’t got a clue how to get
there! So if anyone can help me, you’d be a star!

Thanks in anticipation.
Jeanette

 

[Jeanette]

Sorry to take so long, got tied up on some other stuff.  Anyhow...

Replace the code I gave you before with the code before.  You won't need
your 3rd button.  Instead, when a match is found a prompt will come up asking
whether or not to continue searching.  You simply respond by clicking the
[Yes] or [No] button in the prompt window.

Private Sub CommandButton1_Click()
  'the "search" button
  'will look for last name entries first
  'if match is found, stops completely
  'if no match found, checks to see
  'if there's an entry in first name box
  'and if there is, will search for match
  '
  Const whatColor = 3 ' 3=red
  Dim searchList As Range
  Dim anyEntry As Range
  Dim findEntry As String
  Dim whatToDoNow As Integer

  '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
          Application.Goto anyEntry, True
          anyEntry.Activate
          'stop or continue searching for last name
          If MsgBox("Continue searching?", vbYesNo, "What Now?") <> vbYes Then
            Set searchList = Nothing
            Exit Sub
          End If
        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
            Application.Goto anyEntry, True
            anyEntry.Activate
            'stop or continue searching for last name
            If MsgBox("Continue searching?", vbYesNo, "What Now?") <> vbYes
Then
              Set searchList = Nothing
              Exit Sub
            End If
          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
            Application.Goto anyEntry, True
            anyEntry.Activate
            'stop or continue searching for last name
            If MsgBox("Continue searching?", vbYesNo, "What Now?") <> vbYes
Then
              Set searchList = Nothing
              Exit Sub
            End If
          End If
        End If
      Next
    End If
  End If
End Sub

Private Sub CommandButton2_Click()
  'the "cancel" button
  Unload Me
End Sub

Private Sub UserForm_Initialize()
  Me.TextBox1 = "" ' last name box
  Me.TextBox2 = "" ' first name box
End Sub

Jeanette,
I don't know how much you know/don't know about user forms, so I'llgo
through this in some detail and you can skip over the parts you don'tneed.  
If there are parts you need more help with, just ask.
First, you'll need a way to open your userform once you get it created.  For
that we need a regular subroutine.  Open your workbook and press [Alt]+[F11]
to open the VB editor.  In its menu, choose Insert --> Module and copy and
paste this code into it:
Sub OpenUserForm1()
  UserForm1.Show
End Sub
Next, still in the VB editor, choose Insert --> UserForm  it shouldinsert a
form named UserForm1 for you.  Use the toolbox to add a label and change the
text in it to something like "Surname" or "Last Name".  Add a textbox next to
it, that text box's name will be TextBox1.  Repeat this process to add
another label ("First Name" in it) and another text box that should be
TextBox2.
We're ready for the 2 command buttons:  put one on the form and setits text
to something like "Search", and set the second one's text to something like
"Cancel/Close".
Right-click in a clear area anywhere on the userform and choose [ViewCode]
from the list.  A module will open up with a 'stub' entry for
UserForm_Click() created.  You don't need that.  Just copy all the code below
and paste it over anything that the editor provided for you.  Your userform
will be ready to use then.  To try it all out, close the VB Editor and then
from the Excel worksheet with your list of names on it, use Tools -->Macro
--> Macros to highlight the OpenUserForm macro and use the [Run] button to
open it up for use.  Here's the code for the userform:
Private Sub CommandButton1_Click()
  'the "search" button
  'will look for last name entries first
  'if match is found, stops completely
  'if no match found, checks to see
  'if there's an entry in first name box
  'and if there is, will search for match
  '
  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
End Sub
Private Sub CommandButton2_Click()
  'the "cancel" button
  Unload Me
End Sub
Private Sub UserForm_Initialize()
  Me.TextBox1 = "" ' last name box
  Me.TextBox2 = "" ' first name box
End Sub
:
I'd really appreciate some help...
I'd like to create a userform with two input boxes - one for Surname,
one for Firstname, two CommandButtons, one to perform action, the
other to cancel.
The action I want to perform is for the user to type a surname in
inputbox1 and then do a search in column B for that surname, that's
NOT red/strikethrough OR for the user to type a Firstname in inputbox2
and do a search for that firstname in column C that's NOT red/
strikethrough.  If one of you brilliant techies can help me, I'd be
ever grateful!!- Hide quoted text -
- Show quoted text -

JLatham very kindly helped me out with this and I have replied in the
Group, but no-one is able to help me so I'm posting this again.  I’m
hoping that someone could tweak this code so that I can keep
performing the same task until Ifindthe entry I want (you see, some
people may have the same surname or the same firstname) – i.e., search
again but from the activecell/current position.  Here’s how I need
for it to work:

1.Findsome surname in column B that’s not red strikethrough
2.Go

...

read more »- Hide quoted text -

- Show quoted text -

A personal BIG thanks to JLatham - it works a treat! You have been a
great help and I really appreciate the time you've spent in helping me
out.
Ever grateful... Jeanette