Need help woth vb script

Microsoft Excel 2003 (full product)
October 5, 2009 at 10:23:06
Specs: Windows XP
I need a vbs that will look in two cells in fileA(first and last name) and then search fileB(two cells) for that first and last name combo. if exists do nothing if it does not exist in fileB highlight the row that the name appeared in fileA.
Thanks for the help.

See More: Need help woth vb script

Report •

October 5, 2009 at 11:04:52

Instead of using a visual basic macro which will have to be run every time you change either of the lists (A or B), you can use Conditional formatting.

You have two groups of names - 'existing', which you check against and 'new' which have to be checked against the 'existing' list.
The new names are organized with first names in column B and last names in column C

1. Select the column of first names in the 'existing' list. Name this selection "FirstNamesDB"
To name a range, first select it, then Insert - Name -Define. The address range should already be in the box at the bottom. Enter the name at the top and click Add, then OK
2. Repeat for the column of last names in the 'existing' list. Name this selection "LastNamesDB"
3. Select the whole table of 'existing' names (2 columns). Name this selection "AllNamesDB"

4. Go to the first cell containing a new name (First name).
For this example the table of new names starts on row 23, so the first new 'first name' is in cell B23.

4a. Select Format - Conditional Formatting...
4b. Under 'Condition 1' select 'Formula Is' from the drop-down list
4c. In the formula box enter the following:

Note 1: This is all one line, to go in the formula box
Note 2: Make sure the $ signs are used as shown - this allows the conditional format to be copied to the other cells and maintain the correct cell references.
Note 3: TRUE and FALSE do not have quotes around them - they are logical states.
Note 4: The names you gave to the 'existing names' ranges do not have quotes

4d. Select the format you want to show when a First name/Last name pair does not exist in the existing names list.
4e. click OK

5. Select the cell you have just added the conditional formatting to, and Copy it
6. Select all the cells in the new list (two columns) and use Paste Special - Paste Formats.

7. When a name pair does not exist in the existing names list, the first and last names will show your selected format.

Basically this works by searching for both first and last name in the two columns of existing names using Vlookup. If either name is not present Vlookup gives an error and returns TRUE. If both names occur in their respective columns then the final Vlookup checks that they are both on the same row. If they are present but not on the same row, it returns TRUE. When the formula returns TRUE it triggers the conditional formatting.


Report •

October 6, 2009 at 09:35:58
I like the idea but I do not know if it will work with two diffrent files? Am I missing something?

Report •

October 6, 2009 at 10:52:31

You referred to two cells in each 'file'.

I presume that you have your files loaded in Excel so that the data is in cells.

If that is not the case please let us know what data you are handling and where it is.


Report •

Related Solutions

October 6, 2009 at 12:19:36
I have two excel files FileA and FileB both have multiple sheets. I need a way to look at FileB (a subset of FileA) and highlight the diffrence in FileA. Both are located on the C:. just diffrent names. I have been working with the vlookup but I had to cut and paste into a new sheet. I know that I am missing it I just do not know what it is.

Thanks for the help

Report •

October 6, 2009 at 13:31:56

OK, so you have two Excel workbooks which we can call A.xls and B.xls

B.xls is a subset of A.xls

You need to highlight all names in A.xls that are missing from B.xls

To use conditional formatting, the named ranges must be in the same workbook as the cells with the conditional formatting, so you must copy the names from B.xls into A.xls

So just add a new worksheet to A.xls, call it BNames.
Copy the names from B.xls into the sheet BNames

Create the named ranges on sheet BNames for First names, Last names and All Names as already described.

Add the formula to the cells with first and last names in the original A.xls worksheets.

Names in the original A.xls that are not on the sheet BNames will be highlighted.

The formula I suggested also works as an ordinary formula in a cell. This is easier to test than when the formula is in the conditional formatting formula box.

For example First name in cell B3 and last name in cell C3 (names from the longer A.xls data) and put this formula in cell D3

This is all one formula - I just split it to make it easier to read.

This formula will return TRUE for any name that is not in the list on sheet BNames.


Report •

October 6, 2009 at 13:33:37
here is what I hve tried but it still does not return that it found the alpha/numeric value that I know is there.

= IF(ISNA(VLOOKUP('[test1.xls]Level 1'!$N$2,Sheet1!C2:E2000,3,FALSE)) = TRUE, "Employee not found", VLOOKUP(15,Sheet1!E2:E2000,1,FALSE))

I want to know if a person, by employee number exists in the second sheet.

Report •

October 6, 2009 at 14:15:39

You changed the question. This is now about looking up an employee number? ... and what is the alpha/numeric value - you didn't mention this before.

In your formula you appear to be looking up something possibly a name which is in cell N2 in [test1.xls]Level 1.

You are looking for this name in the first column of the range:

i.e you are looking in column C (Vlookup always searches for the match in the first column of the range)

If there is no match for the name or whatever data is in N2, anywhere in column C from C2 to C2000, your formula returns "Employee not found"

If there is a match then you run the second Vlookup which is looking for the numeric value 15 in column C, between C2 and C2000.

If it finds 15 in Column C it will return the value 15 as you have specified column 1 as the column to return the value in the Vlookup function.

1. Does column C on sheet1 contain what you are looking for from cells N2 in the Test1.xls Level1 worksheet?

2. When you find a match for whatever was in N2 are you trying to return a value from the same row.

Suggest you give an example of what is in a row in the C2:E2000 range.

BTW you don't need the =TRUE in your formula.
If Vlookup returns the NA error, the ISNA function returns true for you. You don't need to retest it.


Report •

October 7, 2009 at 06:27:28
Thanks for the help. I was looking at employee names. I was able to copy them to a new sheet and use vlookup and get the results that I wanted.

Then instead of looking at two diffrent fields per sheet, I wanted to change it to the position number(I thought that would be easier). I understood vlookup to look at the left most column and would return any thing in that row(still within the selection). So I wanted to look at the array for the number(left most column) and return the name associated with that number(3 columns from the left contung the first column), I will look at the code more this am.

Thanks ALOT for the help

Report •

October 7, 2009 at 08:18:21
Thanks for the help. I got it to work as I would like. Without forums such as this it would be very difficult. MAKE it a great day

Report •

Ask Question