Macro to force format and check barcode check digit

Hi,

My company uses 4 types of barcodes 8, 12, 13, & 14 number barcodes for our products my problem is that I can't figure out how to force the barcode to format properly no matter how somebody enters it if they don't use spaces or put them in the wrong spots, I can't use custom formats because there is 4 different layouts

8 digit should be "#### ####"
12 digit should be "###### ######"
13 digit should be "# ###### ######"
14 digit should be "# ## ##### ######"

these barcodes are in columns L, M, & N also right now 'm using a formula in another cell to verify the barcodes by calculating the check digit and comparing it to the check digit typed the formula i'm using is

Please Login or Register  to view this content.

I have that cell change to red if the check digits don't match, but i would prefer to have the cell with the upc change the fill to red if it is typed incorrectly but i don't know how to write the formula in vba. I'd want the macro to run whenever the cell contents are changed. any help would be much appreciated, i'm pretty new to VBA and I just can't figure this one out
or find out how to do it on the net.

Thanks,

Chris

Maybe like this, assuming none of the barcodes have leading zeros. Code goes in the Sheet module:

Please Login or Register  to view this content.

Adding Code to a Sheet module
1. Copy the code from the post
2. Right-click on the tab for the relevant sheet and select View Code. This opens the Visual Basic Editor (VBE) and shows the object module for the selected worksheet.
3. Paste the code in the window
4. Press Alt+Q to close the VBE and return to Excel

It would be easy enough to add the check digit processing if you explain the algorithm (or provide a link) so I don't have to dissect your formula.

Maybe like this. I confess to minimal testing. Format columns L:M as text beforehand.

Please Login or Register  to view this content.

Post a workbook and explain how to break it.

Bug in the code; try this:

Please Login or Register  to view this content.

BTW, the conditional formatting you're using for alternate line shading will override the cell color applied in the code.