VBA Search box, button and highlight

[jca0078]

Hi,
I need your help to simplify my searches. The goal is to have a text box where I can input a word withing my sheet and when I press the Search button it highlights all the cells with the same search value in yellow. So far I have a text box (TextBox1), a Search button, and a reset button. I'm stock trying to attach the text box to the button's macro.

The macro attached to the Search button is the following:

Sub Macro1()
'
' Macro1 Macro
'
 
'
     With Application.ReplaceFormant.Interior
             .PatternColorIndex = xlAutomatic
             .Color = 65535
             .TintAndShade = 0
             .PatternTintAndShade = 0
     End With
     Cells.Replace What:="Paul", Replacement:="Paul", LookAt:=xlPart, _
              SearchOrder:=xlByRows,
              MatchCase:=False, SearchFormat:=False, _
              ReplaceFormat:=True
End Sub

Any idea in how to have this macro look up the word within my text box instead of looking up a specific value such as "Paul"?

 

[mikerickson]

Have you considered using a cell instead of a text box and Conditional Formatting (with a formula like =($A$1=B2) ) instead of a button?

 

[jca0078]

Have you considered using a cell instead of a text box and Conditional Formatting (with a formula like =($A$1=B2) ) instead of a button?

Thanks Mike for your quick response. I don't quiet follow what you mean with your formula (I'm a newbie!).

My sheet is a name list with activities. So I would like to enter part of the name on a text box (i.e.) and have that name highlighted wherever it appears in other cells, so I can quickly visuallise what that person is participating in. I don't want to sort, I just want a quick highlight of my search result. Thanks a lot!

 

[mikerickson]

If you have your activities in A3 and downward. And your search term in A1.
Select A3 and set Conditional formatting to the formula =ISNUMBER(MATCH("*"&$A$1&"*", A3, 0))
Copy that formatting to the rest of the cells in the data region and it will highlight partial matches. (Neither native Excel nor VBA will highlight as you type the search term, you need to press enter.)

<table border=1 cellspacing=0>
<tr align="center" bgcolor=#A0A0A0><td width=25> <td width=25><b>A</b></tr>
<tr><td align="center" bgcolor=#A0A0A0><b>1</b><td align="left" bgcolor=#FFFFFF>swim</tr>
<tr><td align="center" bgcolor=#A0A0A0><b>2</b><td align="left" bgcolor=#FFFFFF></tr>
<tr><td align="center" bgcolor=#A0A0A0><b>3</b><td align="left" bgcolor=#FFFF00>Swimming</tr>
<tr><td align="center" bgcolor=#A0A0A0><b>4</b><td align="left" bgcolor=#FFFFFF>Rowing</tr>
<tr><td align="center" bgcolor=#A0A0A0><b>5</b><td align="left" bgcolor=#FFFF00>Swimming</tr>
<tr><td align="center" bgcolor=#A0A0A0><b>6</b><td align="left" bgcolor=#FFFFFF>Fishing</tr>
<tr><td align="center" bgcolor=#A0A0A0><b>7</b><td align="left" bgcolor=#FFFFFF>Rowing</tr>
<tr><td align="center" bgcolor=#A0A0A0><b>8</b><td align="left" bgcolor=#FFFFFF>Canoeing</tr>
</table>

 

[jca0078]

Mike,
I really appreciate your help. Your suggestion is not really what I'm looking for. If you look at my code above, what I'm trying to figure out is how to replace the What:="Paul", for the What:...to be the typed word inside my text box. I'm simply trying to simplify what the Find/Replace and Format command does. thanx again!

JCA

 

[mikerickson]

Something like

Dim searchTerm as String

SearchTerm = InputBox("Enter something")

If StrPtr(searchTerm) = 0 then Exit Sub: Rem cancel pressed

Range("A:A").Find(what:=searchTerm)

 

[jca0078]

Awesome!
Could I just replace the InputBox by my textBox1? What would the code look like?

 

[mikerickson]

What kind of text box do you have?

 

[jca0078]

What kind of text box do you have?

I inserted an ActiveX control Text Box (TextBox1) into my sheet. I'll like to type my search word there and then hit the search button to accomplish what we did with the previous codes. Thx

 

[mikerickson]

My Mac doesn't support ActiveX.