macro search

Hi,
I have a group of account numbers that I need to search for everytime I
open a certain worksheet. I would like it to automatically search for
the account numbers listed then highlight it in yellow to alert me.

I hope somebody can help me, bec. it's a pain doing the ctrl F
then hitting backspace to erase it then entering the next account
number.

thanks!
guideme

Hi Guideme,

Try:

Sub Tester()

Dim AccNo As String
Dim RngAcc As Range
Dim firstAddress As String
Dim c As Range
Dim Arr As Variant
Dim i As Long

Arr = Array("A1001", "A1005", "A1010") '<==== CHANGE

Set RngAcc = ThisWorkbook. _
Sheets("Sheet1").Range("A1:A200") '<==== CHANGE

Application.ScreenUpdating = False

'Clear pre-existing highlights
RngAcc.Interior.ColorIndex = xlNone

For i = LBound(Arr) To UBound(Arr)
AccNo = Arr(i)

With RngAcc
Set c = .Find(AccNo, LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
c.Interior.ColorIndex = 36
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address _
<> firstAddress
End If
End With
Next i

Application.ScreenUpdating = False

End Sub

Change the Arr value to accord with the required account numbers.
Change the RngAcc address to match the full address of the range holding
account numbers. I have assumed a single column range as this seemed to me
likely to correspond with your situation; the RngAcc can, however, be set
to any range (or ranges) that you wish.

If the account numbers to be found are always the same, the array approach
adopted here would be very suitable. If, however, these are subject to
frequent change, the macro could readily be adapted to read the required
account numbers from a worksheet range. Alternatively, an inputbox could be
popped up.

In any event, try this routine on a copy of your workbook until you are
happy with the results.

---
Regards,
Norman



"guideme" <[email protected]> wrote in message
news:[email protected]...
> Hi,
> I have a group of account numbers that I need to search for everytime I
> open a certain worksheet. I would like it to automatically search for
> the account numbers listed then highlight it in yellow to alert me.
>
> I hope somebody can help me, bec. it's a pain doing the ctrl F
> then hitting backspace to erase it then entering the next account
> number.
>
> thanks!
> guideme
>

Hi Guideme,

Typo warning!

Change the penultimate line of the macro from:

> Application.ScreenUpdating = False

to

Application.ScreenUpdating = True

---
Regards,
Norman



"Norman Jones" <[email protected]> wrote in message
news:[email protected]...
> Hi Guideme,
>
> Try:
>
> Sub Tester()
>
> Dim AccNo As String
> Dim RngAcc As Range
> Dim firstAddress As String
> Dim c As Range
> Dim Arr As Variant
> Dim i As Long
>
> Arr = Array("A1001", "A1005", "A1010") '<==== CHANGE
>
> Set RngAcc = ThisWorkbook. _
> Sheets("Sheet1").Range("A1:A200") '<==== CHANGE
>
> Application.ScreenUpdating = False
>
> 'Clear pre-existing highlights
> RngAcc.Interior.ColorIndex = xlNone
>
> For i = LBound(Arr) To UBound(Arr)
> AccNo = Arr(i)
>
> With RngAcc
> Set c = .Find(AccNo, LookIn:=xlValues)
> If Not c Is Nothing Then
> firstAddress = c.Address
> Do
> c.Interior.ColorIndex = 36
> Set c = .FindNext(c)
> Loop While Not c Is Nothing And c.Address _
> <> firstAddress
> End If
> End With
> Next i
>
> Application.ScreenUpdating = False
>
> End Sub
>
> Change the Arr value to accord with the required account numbers.
> Change the RngAcc address to match the full address of the range holding
> account numbers. I have assumed a single column range as this seemed to me
> likely to correspond with your situation; the RngAcc can, however, be set
> to any range (or ranges) that you wish.
>
> If the account numbers to be found are always the same, the array approach
> adopted here would be very suitable. If, however, these are subject to
> frequent change, the macro could readily be adapted to read the required
> account numbers from a worksheet range. Alternatively, an inputbox could
> be popped up.
>
> In any event, try this routine on a copy of your workbook until you are
> happy with the results.
>
> ---
> Regards,
> Norman
>

THANKS! i'll try it.