I have an excel sheet with various items in our inventory, these items are named by their resource id. I use a barcode scanner to scan the label on an item by using the ctrl+f function scanning the barcode and finding that number on the excel sheet. Then i highlight the resource. Is there a way to scan the barcode number and if it finds it on the excel sheet highlight it by itself?
Excel VBA Course - From Beginner to Expert
200+ Video Lessons
50+ Hours of Video
200+ Excel Guides
Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)
Highlight ID by itself
0
Answers
0
Selected Answer
can you show example? Upload file
Discussion
Right know im using macros to highlight and undo to make it a little faster.
kmaldon2 (rep: 2) Jul 24, '17 at 2:55 pm
REVISED (to help show solution without trudging through posts):To Highlight a searched (aka Find) cell, use VBA Noob's Macro below:
you could have a Macro to do a FIND and then highlight the MATCHING info. Something with .Interior.ColorIndex?
Sub HighlightCells()
Dim i As Long
Dim Fnd As String
Dim fCell As Range
Fnd = InputBox("Enter text!", "Search text")
Set fCell = Range("A1")
For i = 1 To WorksheetFunction.CountIf(ActiveSheet.UsedRange, Fnd)
Set fCell = Cells.Find(What:=Fnd, After:=fCell, _
LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False)
If fCell Is Nothing Then
Msgbox fnd & " not on sheet !!"
Exit Sub
Else
With fCell
.Interior.ColorIndex = 6
End With
End If
Next i
End Subyou could have a Macro to do a FIND and then highlight the MATCHING info. Something with .Interior.ColorIndex?
queue (rep: 467) Jul 24, '17 at 3:09 pm
how do i do that? is it like the highlight macro?
kmaldon2 (rep: 2) Jul 24, '17 at 3:12 pm
VBA Noob posted this a bit ago....Sub HighlightCells()
Dim i As Long
Dim Fnd As String
Dim fCell As Range
Fnd = InputBox("Enter text!", "Search text")
Set fCell = Range("A1")
For i = 1 To WorksheetFunction.CountIf(ActiveSheet.UsedRange, Fnd)
Set fCell = Cells.Find(What:=Fnd, After:=fCell, _
LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False)
If fCell Is Nothing Then
Msgbox fnd & " not on sheet !!"
Exit Sub
Else
With fCell
.Interior.ColorIndex = 6
End With
End If
Next i
End Sub
You could modify your macro for looking up from the Scanning or Manually copy and paste it to this macro.
Dim i As Long
Dim Fnd As String
Dim fCell As Range
Fnd = InputBox("Enter text!", "Search text")
Set fCell = Range("A1")
For i = 1 To WorksheetFunction.CountIf(ActiveSheet.UsedRange, Fnd)
Set fCell = Cells.Find(What:=Fnd, After:=fCell, _
LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False)
If fCell Is Nothing Then
Msgbox fnd & " not on sheet !!"
Exit Sub
Else
With fCell
.Interior.ColorIndex = 6
End With
End If
Next i
End Sub
You could modify your macro for looking up from the Scanning or Manually copy and paste it to this macro.
queue (rep: 467) Jul 24, '17 at 3:16 pm
Thanks!!!
kmaldon2 (rep: 2) Jul 24, '17 at 3:24 pm
Hi queue, if you could, please update your answer with the solution so future readers don't have to scroll through the discussion to find it. I don't know what's the best format, on here for answers, but I'm at least trying to keep things consistent :/.
don (rep: 1989) Jul 24, '17 at 3:58 pm