Comparing 2 lists and highlighting cells.

[steveh8204]

Hi, I am new to Vba and have picked up a lot but I still need some help with a spreadsheet I am putting together.

I have two sets of stock sheets (on separate worksheets) in the same file that I need to compare to see which ones exist in one that don't in the other and then highlight them.

I have made huge strides with using v-lookups inside macro modules to compare quantities that do exist in both (and compare) but I'm still not sure of the best way to highlight new ones as described above.

Any help with this would be appreciated.

Thanks in advance.

 

[Yongle]

I cannot tell what you are trying to compare with what from your description.
So I have assumed that you want to compare the stock item in column A in sheet1 with the stock item in column A in sheet2
You do not need VBA - conditional formatting can do this

- select range in sheet1 starting with A2 (allow for expansion - so I selected A2:A5000)
- click on ConditionalFormatting \ New Rule \ Use a formula... \ enter formula below \ and format as you want
=COUNTIF(Sheet2!A:A,A2)>0

- select range in sheet2 starting with A2 (allow for expansion - so I selected A2:A5000)
- click on ConditionalFormatting \ New Rule \ Use a formula... \ enter formula below \ and format as you want
=COUNTIF(Sheet1!A:A,A2)>0

If I have misinterpreted, please explain and include column letters etc in the explanation
thanks

 

[steveh8204]

Hi,

Sorry yea probably could have explained a bit better. Typing a post on my phone with the missus in my ear ends up like that sometimes.

I'll try that after, I hadn't considered just using conditional formatting.

I've basically got two lists of stock counts from different sources (a web source and Sap) with a stock code and a quantity for each which 'should' be the same. I've managed to write code to lookup matching stock codes and compare quantities but if one code appears on one and not the other I need a way of highlighting it.

When I get home I will try that conditional formatting. Thanks again.

 

[steveh8204]

Just tried it now and it seems to do the job. It highlights everything that matches but I was hoping to just highlight the ones that are only on one sheet. It's there a way to 'invert' the conditional formatting for this?

 

[steveh8204]

Right Ive just tried this conditional formatting and it doesnt work anymore. I think its because I need to compare date from two different sheets against one which should contain them all. To clarify:

List A: Contains all stock from one (web based source)
List B: Contains all stock from SAP system of one type (Raw Materials)
List C: Contains all stock from SAP system of one type (Packaging)

(every list is a separate Worksheet, all in one Workbook file)

I need some conditional formatting (or VBA) that will go through every line on List A and if it exists on List B or C do nothing, if it isn't on either to highlight it. Just for the first (A) row will do.

Any further help with this issue would be appreciated as it's the last step in my spreadsheet which is saving me a considerable amount of reconciliation work which is usually painstaking.

Thanks

 

[Fluff]

How about
=COUNTIF(Sheet2!A:A,A1)+COUNTIF(Sheet3!A:A,A1)=0

 

[steveh8204]

It doesn't appear to work, some very random things happening as well.

Would it be easier to put any unique cells from List 1 (ones that don't appear in 2 or 3) into a new sheet 4?

This was my original plan to have a vlookup in the vba code to find any unique (stock) codes and put them in a new, separate sheet.

I also don't have the stock codes in column A in lists 2 and 3 to make it more complicated. I can work around that it's just pasted from its native source that way so easier if I can ask it to look in column C instead.

Thanks for your help so far it is much appreciated.

 

[Fluff]

To look in col c for Lists B & C change A:A to C:C

 

[steveh8204]

Nice one, thanks. I have got the conditional formatting workimg now. I had some problems selecting the ranges. Would be real handy if you could 'invert' the highlighted cells but that will do.

Thanks again.

 

[Fluff]

As you said

that will go through every line on List A and if it exists on List B or C do nothing, if it isn't on either to highlight it.

It should highlight anything on list A that is not on lists B & C.
If you want it the other way round the change =0 to >0