VLOOKUP and highlight row

[ChicagoPete]

Hi gang,
So I have a spreadsheet with 20,000 items listed by UPC code. The top 5 rows
are in freeze pane mode with some direction as how to use, in cell B3 I have
a empty cell where the user enters a UPC number, the cell next to it has a
vlookup function that finds the item and returns the wholesale cost found in
column 6 - easy enough. =VLOOKUP(B3,A6:J20012,6,FALSE)
I would like it IF when vlookup finds the UPC code to highlight the entire
row because the user may need to look at other data. As if you were to scroll
down manually, find the row and highlight with a mouse click. So if the item
was in row 15,100 the spreadsheet would jump down and highlight the entire
row. Hope this makes sense - can it be done?

Thanks...

 

[Brad]

I would just use the vlookup to bring all values to row 3, so the user does
not need to pan down to find the information.

If you still want to highlight the row 15,000 rows down, you can try
conditional formatting the entire data set so that it highlights using an if
true or false formula where A#=A3. In other words, in the conditional
formatting, use the pull down menu and set the criteria to formula, then put
"if(A#=$A$3,TRUE,FALSE)" in the right box and set the format to highlight as
desired. (Delete the quote marks. # equals the current row in the data
set.)

Brad

Excel 2002 on XP Pro SP 3
Excel 2007 on Vista 64

 

[John]

Hi Pete
If you want to go down to the select choice, you can hyperlink, something like
this formula. You may need to adjust the range.
=IF(B3="","",HYPERLINK("#A"&MATCH(B3,A6:A20012,0)+1,"Go To:"))
HTH
John

 

[Bernard Liengme]

Select A6:J20012
In the conditional formatting dialog specify the Formula Is (or in Excel
2007, "Use formula to determine ...)
Enter =$A6=$B$3 and select the required formatting
Note the $ before A - this is needed to get the whole row to be formatted

best wishes
Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme

 

[ChicagoPete]

Thanks for the quick reply Brad, I'll just use vlookup to bring more values
up...

-Pete

 

[ChicagoPete]

Bernard,
Yes, this works great to format the entire row, the second "wish" was to
have Excel jump down and show that row (if it happened to be row 9,999)
instead of scrolling down.

Thanks for your input!

-Pete

 

[ChicagoPete]

Hi John,
Thanks for the Hyperlink tip - never used that b4. It did take some range
adjustments but I got it to work and combined with Bernard's formatting this
should please the beasts, for now...

Thanks to all!

-Pete