Double click cell to relocate active cell

[Ironman]

Hi

Each cell in an expanding named range "AnalysisRoutesList" in Col E has data validation, so when a cell is double clicked, an Information msgbox appears (OK, Cancel, Help) prompting me to double click the cell 3 columns to the left of the one I have just double clicked (Col B).

What I'd be grateful for is some code so when I double click any cell in the above named range (it expands by inserting rows within the range, not at the bottom), the cell in the same row in Col B is selected (or better still, double clicked).

(Double clicking a cell in Col B runs a search macro, but it fails to locate the correct cell fairly frequently, hence why I don't simply double click the cell in Col B - but I need to do it this way because the code associated with double clicking the cell in Col B runs perfectly every time when I double click AnalysisRoutesList cells first, then go back to Col B).

Many thanks!

 

[Siddharth Rout]

Hey Sid that works great, thank you!

Would it be possible for you to modify your code just slightly, so if I get the error when double clicking Col B, then it automatically runs the same search function in the named range in Col F as if I double clicked it myself, instead of me needing to manually double click it myself?

Thanks again, you're almost there!

What do you mean by "Getting an Error"? Can you explain that bit more?

 

[Ironman]

Sorry, it's not a VB error, but it is an error because it appears even when a cell within the relevant range has been selected:

Else
           'MsgBox searchStr & " not a valid route - route search function only", vbInformation, "Route Locator"
            MsgBox "Invalid search - double click ROUTE # cell only", vbCritical, "Invalid Cell Selection"

Can the msgbox be substituted to re-running the same search function in Col F i.e. if I double click Col B and for an unknown reason it won't perform the search (incorrectly can't find the cell), then instead of getting the msgbox the macro then 'tries again' by 'double-clicking' the same row in Col F to run the find function, which always works when I do this manually.

 

[Siddharth Rout]

Sorry, it's not a VB error, but it is an error because it appears even when a cell within the relevant range has been selected:

Else
           'MsgBox searchStr & " not a valid route - route search function only", vbInformation, "Route Locator"
            MsgBox "Invalid search - double click ROUTE # cell only", vbCritical, "Invalid Cell Selection"

Can the msgbox be substituted to re-running the same search function in Col F i.e. if I double click Col B and for an unknown reason it won't perform the search (incorrectly can't find the cell), then instead of getting the msgbox the macro then 'tries again' by 'double-clicking' the same row in Col F to run the find function, which always works when I do this manually.

If I have understood you correctly, then replace the MsgBox line with

findRange Range("F" & rngRange.Row)

 

[Ironman]

Hey Sid, I think you've done it!

Thank you so much for all the time and perseverance you've put into answering my question - I know it hasn't been easy.

Best regards

Paul