overide conditional formatting with VBA

**Code Flunkie** · 03-10-2010, 05:58 PM

I have a macro that checks a barcode to see if it a valid barcode. if it isn't it makes the cell red to show there is an error. this all works great except that I use conditional formatting to color band every second row to make the data stand out because there is over 3000 lines in the spreadsheet. my problem is that while the if an error occurs where there is no color it is fine but if there is color it hides the error color so the error doesn't show. is there a way around the conditional formatting either by doing the color banding with VBA or to makeconditional formatting's priority lower. any help would be much appreciated. i've included the code i use below.

Thanks in advance,

Chris

Please Login or Register  to view this content.

Please Login or Register  to view this content.

**shg** · 03-10-2010, 06:11 PM

You could create a simple UDF to return true for a valid barcode based on length and checksum, and add that to the existing conditional formatting.

**Code Flunkie** · 03-11-2010, 09:45 AM

I'm pretty new to VBA, so how would i do that?

**shg** · 03-12-2010, 03:57 PM

Change the code in the sheet module to this:

Please Login or Register  to view this content.

Then put this in a code module:

Please Login or Register  to view this content.

Then select A1 on Sheet1 and do Insert > Name > Define

Sheet1!Me Refers to:=A1

frmChkBad Refers to: =NOT(IsValidChecksum(Sheet1!Me))

Then select the relevant portions of columns L/M/N, and do Format > Conditional Formatting,

Formula is =frmChkBad and format as red

You can integrate the color banding after you get all that straight.

**Code Flunkie** · 03-12-2010, 05:37 PM

I'm having issues with:

Then select A1 on Sheet1 and do Insert > Name > Define

Sheet1!Me Refers to:=A1

frmChkBad Refers to: =NOT(IsValidChecksum(Sheet1!Me))

Then select the relevant portions of columns L/M/N, and do Format > Conditional Formatting,

Formula is =frmChkBad and format as red

i'm using Office 2007 so i went formulas>defined names> define names but it giving me an error when i try to put in it says it's not a valid name. what am i doing wrong