Changing "value" into "any value in a specific cell" in VBA

[zeghze]

Hello everyone!

I want to build a macro or use a formula which allows me to search the value of (let's say)"search" cell K10 in a table spreadsheet. Jump to it, replace the background with color green, and jump to the cell next to it to the right (The value of K10 is in E171, fills the background green, then jumps to F171)

Then I want to build another macro (or associate a key combination) which brings me back to K10 regardless of where I am in the spreadsheet.

So far this is where I am:
Sub find_fill_jump()
'
' find_fill_jump Macro
'


'
ActiveCell.Offset(-14, 5).Range("A1").Select
ActiveCell.FormulaR1C1 = "gohy53-10775-ae"
ActiveCell.Select
Selection.Copy
Cells.Find(What:="gohy53-10775-ae", After:=ActiveCell, LookIn:=xlFormulas _
, LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.Replace What:="gohy53-10775-ae", Replacement:="", LookAt:= _
xlPart, SearchOrder:=xlByColumns, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=True
Cells.FindNext(After:=ActiveCell).Activate
ActiveCell.Offset(13, -5).Range("A1").Select
End Sub

I want to replace "gohy53-10775-ae" into value=K10 in the code, but I couldn't find the appropriate command or magic word on google or anywhere . online. Could you please help me? I spent almost 8 hours of search and I failed to find it.

Thank you in advance!
Viktor

 

[Fluff]

If you just want to highlight a cell, you can do that with conditional formatting.
Select the cells concerned > Home Tab > Conditional Formatting > Highlight cells rule > Equal to > =$K$10 > select the format you want > OK

 

[zeghze]

Thank you very much! Works nice and smooth, but the problem is that it highlights all the cells associated to the value, not just one of them. I clarify my project, so it makes more sense. The company I work for has a 20th century technology for locating a certain product in a ~250 delivery list. Basicly, you aproach a product, find it among a list of 20 A4 pages, and you put it in details next to it like how many containers have arrived, and how many items are in that containers.
To make it even more difficult, the product is not even in alphabetical or numerical order, so you have to browse through all thoose pages until you find the product. And the products as well organised in a completely unorganised state per pallet. All of them have barcodes. So what I thought is that I connect a scanner to my laptop, scan the product, search it in the spreadsheet, highlight the product with green background, give the required details next to it.
So I want to make one cell a "search" cell. In the search cell I put the product name by scanning it's barcode, then I activate a macro which jumps to the cell with the matching value in the list, fills it with green background, then I enter the required details manually. Once I finish, I press a hotkey, which returns me to the "search" cell.
So in general, I cant highlight all the cells with the associated value, because if I find that product again in a different pallet, then I won't be able to quicklocate it anymore with conditional formatting (the same product can arrive in a different container, and I need to be specific with the details)

I hope it makes more sense now, and sorry to be a pain

 

[Fluff]

If the search value can appear more than once, how does the code know which "match" to highlight?

 

[zeghze]

**** it, you're right. I will also have to set a background color to the table, and the search cell, and tell the macro to match the value and the background. I guess I have to stick with search and replace, because that's the tool that does the job.
Can I tell the macro to consider the value changing and the background constant?