How to direct jump on specific Cell in Excel

Question

Good Morning/Afternoon/Evening to all :)

My First time on here so.................... :)

Spreadsheet Example

I use this code in "Conditional Formatting"

=AND(ISNUMBER(SEARCH($B$3,A6)),$B$3<>””)

so i only able to highlight the cell as on Screenshot (Whenever type text on B3 it's automatically highlight the contain Cell as on Screenshot) but what I want is it's only highlight the cell but cannot jump (select) cell when i type ""text"" on Search B3 box.

For Example: On Screenshot I type DW353 and It's highlight in Redcolor on A18 Cell No. but if i type other text as DW364, 365 and on which located on A24, A25 and continue to down, it's only highlight the specific Cell and I have to Search (Scroll up and down) for look that RED Highlight. What i want is whenever i type a "Text" on B3 it's Highlight and direct Jump on Contain Cell as (Find and Replace).

Thank you in Advance and Sorry for Long Question :)

Answer 1

The easiest way to accomplish this kind of functionality is to use VBA to trigger the Advanced Filter functionality, so that all other rows are hidden.

Here's how:

1. Add a Named Range called "MyList" covering the entire range of data in the MACHINE NO: column that you want to filter. (Best if you turn this block of data into an Excel Table, and then just reference the MACHINE NO column, as this will mean the named range is dynamic i.e. it will automatically adjust should the underlying data grow).
2. Put the text "MACHINE NO:" in B2
3. Add a Named Range called "Criteria" covering B2:B3
4. Add a Named Range called "Input" covering B3

This should look something like the below:

5. Put this code in the Sheet Module that corresponds to the Per Machine Movement tab (and NOT in a standard Code Module) :

   Option Explicit
   Private Sub Worksheet_Change(ByVal Target As Range)
   If Not Intersect(Target, Range("Input")) Is Nothing Then
       On Error Resume Next
       ActiveSheet.ShowAllData
       On Error GoTo 0
       Range("MyList").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Range("Criteria"), Unique:=False
   End If
   
   End Sub
   

   End Sub

i.e. like so:

Now, any time someone enters a Machine Number, the sheet will be filtered to show just the row of interest:

You can find a working example at Daniel Ferry's excellent blog, at the following link: http://www.excelhero.com/blog/2010/07/excel-partial-match-database-lookup.html

Look for the second sample file he posted under the heading --- UPDATE ---

This approach can be tweaked to search across multiple columns, as per your follow-up question. First, here's the setup of the Named Ranges (including a new one above the input cell called "Header"):

...and here's the amended code:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim vRanges As Variant
Dim vItem As Variant

If Not Intersect(Target, Range("Input")) Is Nothing Then
    On Error GoTo errhandler
    With Application
        .EnableEvents = False
        .ScreenUpdating = False
        .Calculation = xlManual
    End With

    'Clear any existing filter
    On Error Resume Next
    ActiveSheet.ShowAllData
    On Error GoTo 0

    'Cycle through the search arrays one by one, and run the advanced filter until you find a match
    vRanges = Array("Range1", "Range2", "Range3", "Range4") '<<< Change these to match your range names

    For Each vItem In vRanges
        Range("Header") = Range(vItem).Cells(1)
        Range(vItem).AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Range("Criteria"), Unique:=False
        If Range(vItem).SpecialCells(xlCellTypeVisible).Count > 1 Then Exit For
    Next vItem
errhandler:
    With Application
        .EnableEvents = True
        .ScreenUpdating = True
        .Calculation = xlAutomatic
    End With

End If

End Sub

And here's a couple of screenshots showing it matching on different columns:

You can format cell B2 so that it can't be seen if you want.

I would suggest turning the different input areas in your workbook into Excel Tables (also known as ListObjects) before you set up the "RangeX" named ranges. That's because under the hood, Tables are basically dynamic named ranges that automatically expand to accommodate new data. So if you then manually set up a Named Range that points at a Table column, you never need to remember to adjust your Named Ranges in order to handle new data, because the Table automatically does this for you. See the image below:

Note that you can change the formatting of Tables using the Table Styles option in the ribbon, or even turn off the formatting entirely: