How's this verification done?

[Lava]

I've attached a zip with an Excel file. The guy who made it is no longe
present in the company and to be honest, I have no idea how he did wha
he did.

This excel sheet is used for import. On the right side there's a lis
of valid products with the code. On the import sheet a lot of rows ar
filled out.... the articlecode (artikelcode) which is filled out i
instantly verified against the data on the right... so if someone fill
out the sheet with data and makes a mistake it will display in red. Fil
out code "1232" for example on a row in "artikelcode" and press enter
It'll turn red.

I know how the data on the right side is added to the Excel sheet an
refreshed, but what I do not understand is..... HOW does the validatio
take place? I can't find any macro whatsoever which turns a field in th
column "artikelcode" red as soon as an invalid code is entered (whic
cannot be found on the right).

Could somebody please explain me? :confused

+-------------------------------------------------------------------
|Filename: lava_sdrerxbvs.zip
|Download: http://www.excelforum.com/attachment.php?postid=3904
+-------------------------------------------------------------------

 

[swatsp0p]

Your predecessor used "Conditional Formatting" (Format>Conditiona
Formatting... on the main menu) to test the contents of the cells i
column E as such:

=AND(COUNTIF($J$2:$J$3004,E17)=0,NOT(ISBLANK(E17)))

Which says if the value entered in E17 is not found in column J, tur
the cell RED. The NOT part prevents Blank cells from being turne
RED.

Pretty slick and works well for this task.

HT

 

[Lava]

I see it now... thanks for your quick response!

During my search I also clicked conditioned formatting, but I guess m
focus on the worksheet was wrong as I tried it. Hmmz, yeah it's prett
slick, but like you say it works. I'm glad I finally understand it now
Cheers :

 

[Lava]

With that bit explained, I now got to figure out how to make something
similar by means of a macro. Got a button which is pushed and performs
some validation before the sheet is "qualified" for further processing
(impot into database).

So some maco part which also compares the code-fields with the
product-table or which simple checks if there are any red fields in the
column. Let's see how it can be done.

 

[Lava]

Ended up with a new problem. I maintained the verification/alert idea of
the Conditional Formatting where the field value is checked against a
reference list (with data pulled from a database).

However, due to the new layout of the worksheet it's simply impractical
to have the reference list on the same worksheet. So I thought of moving
it to another worksheet in the same workbook. But I discovered at the
end that Conditional Formatting only works with one worksheet, not with
two (or more).

So, how to make this work? Thinking of macros here taking the range of
another worksheet, but so far no luck with that either. Intersect can
take the whole used part of a column from another worksheet as range
right?

 

[swatsp0p]

A trick to 'fool' CF is to use a Named Range instead of a rang
reference. So, if (for example) you name the range on Sheet2 t
"Table1" then in your CF formula, refer to Table1 instead o
'Sheet2!$J$2:$J$3004', the CF should work. As such:

=AND(COUNTIF(Table1,E17)=0,NOT(ISBLANK(E17)))

Should work, give it a try.

Good Luck

 

[Tom Ogilvy]

It works with defined names. Insert=Names=>Define. have the defined name
refer to the other sheet.

 

[Lava]

Thanks a lot, this worked like a charm.