relatively new to excel and completely new to VBA so i'm really stuck. I'm in the middle of and inventory and trying to make things simpler.
In my spreadsheet i have colB filled with unique asset #s of all the items i should have on the shelf. I am using a barcode scanner to read these numbers into colA.
I have set up 3 conditions and recorded them as a macro. 1) if a unique number then format cell fill color to red 2) if a duplicate number then format cell fill color of the matching cells to green 3) =countif($A:B$, A1)>2 format cell fill color of the matching cells yellow and the text to bold red.
The idea is that when i scan a number and it's supposed to be there the 2 matches turn green, if i have something extra on my shelf that number will make the fill color red in colA. the 3rd condition allows me to know if i scan a number twice because it will turn the new cell and the 2 that have already matched to yellow. If all goes well i should have 2 columns of green cells. But that's dreaming, so if i have any red cells those are the items i have to investigate. Of course there should be no yellows.
In addition I would like to 1) when a number is scanned and entered into colA that number is entered into the colA cell directly beside its coresponding colB cell regardless of which cell the cursor is in prior to the scan. 2) sound an audio arlert when the cells turn yellow so that i don't have to keep looking at the screen when i scan.
I don't know how to modify the macro shown nor do i know how to use VBA to do the things i'm already doing plus the 2 additional conditions.
This is the macro:
thanks bunchesPlease Login or Register to view this content.
Bookmarks