search named ranges with user input

[KrZyMiC]

I need some serious help!!

right now I have a combo box that you are able to select a named range and it will highlight that named range. The problem I have is the combo box list is lonnnngggg.

I wanted to know if it is possible to allow a user to input a named range and have it do the same thing, thus eliminating the long combo box list.

[protonLeah]

How would one know what the range names are?

[KrZyMiC]

okay I will break this down for ya.

I have a bunch of 6x6 squares. Each one of those has a named range.

Basically I'm making a township/range map of the metro area in my state.

I don't know if your familiar with township/ranges but each township/range has 36 sections, thus my multiple 6x6 squares which are broken down into named ranges

named range = township/range

I locate underground facilities and I am part of a district that has 9 supervisor groups. I am attempting to make a sheet that dispatch can use. They will take the township range from the One Call Dig center ticket, input it in text box and then it will highlight that township/range.

each superisor group will be color coded and have certain township/ranges assigned to them, thus when it finds the named range they will know what supervisor to dispatch the ticket to.

Right now I am able to do this, but through the use of a combo box. I was trying to eliminate them having to choose the correct township/range from a long list.

Does that make sense or just make things worse....ha ha

[royUK]

If the naming convention is based on information that the user knows then they can type into a textbox

[KrZyMiC]

EXACTLY!!

Now the info the user inputs in the textbox is a named range.

they enter a township/range - 04WI10N14NE

it will then highlight that named range ( which will be a 6x6 square )

I need to know if that is possible and where to look for help doing that. I have searched and searched and only thing I can find is that you can search a cells data doing this, but nothing on if you can search a named range.

[TechRetard]

could you just use conditional formatting on your 6x6 square...if user enters "xyz" in cell A1 (or w/e cell) then highlight me, else not...do it for each square?

[KrZyMiC]

No I don't believe I can becuase each 6x6 square is broken up in 36 squares, each square with a different number.

I've attached the sheet i'm working on, keep in mind this is far from complete, but it will give you an idea of what the sheet looks like and what i'm trying to accomplish...

as the sheet is now you select the township/range from the combo box and it highlights the 6x6 square according to the named range.

[protonLeah]

User inputs a named range in B112 and presses the "Go" button:

Option Explicit
Sub GetRegion()
    'ben Van Johnson, excel help forum http://www.excelforum.com/
    '16 january 2011
    
    Application.ScreenUpdating = False
    
    Dim RngName         As Range, _
        EastWest        As String, _
        EstWstColumn    As Long, _
        NorSouth        As String, _
        NorSthRow       As Long, _
        CellInfo        As Variant
    
    Set RngName = Range("B112")
    
    'get the main e/w and n/s block coords
    EastWest = Left(RngName, 3)
    NorSouth = Right(RngName, 3)
    
'---------------clear ALL interior colors
    Cells.Select
    Range("B1").Activate
    With Selection.Interior
        .Pattern = xlNone
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
'----------------------------------------

'move to row with e/w coords to start the "find" op

    Range("B115").Select
    
    With Cells
    
        'find the e/w block address and copy the column number
        
        Set CellInfo = .Find(What:=EastWest, _
                            After:=ActiveCell, _
                            LookIn:=xlFormulas, _
                            LookAt:=xlPart, _
                            SearchOrder:=xlByRows, _
                            SearchDirection:=xlNext, _
                            MatchCase:=False, _
                            SearchFormat:=False)
        
        EstWstColumn = CellInfo.Column
        
        'find the n/s block address and copy the row number
        
        Set CellInfo = .Find(What:=NorSouth, After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
        xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
        , SearchFormat:=False)
        
        NorSthRow = CellInfo.Row
        
    End With

    'go to the cell at the intersection of the row and column, resize to six-by=six and color them yellow
    Cells(NorSthRow, EstWstColumn).Select
    Selection.Resize(6, 6).Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 65535
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    Application.ScreenUpdating = True
End Sub

[KrZyMiC]

it is getting hung up at this line

.TintAndShade = 0

the error states "Can't execute code in break mode"

[protonLeah]

Sorry, I'm using xl2007 I guess 2003 doesn't like the interior color and format. Replace

With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 65535
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With

with

    With Selection.Interior
        .ColorIndex = 6
    End With

or delete it altogether, I just used it to highlight the selected block for convenience...