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.

 

[steveh8204]

Right think I've got it now. I find working with CF awkward. All cells in list A are highlighted until I paste others in the other sheets.

But anyway it's all good now. Thanks again for your help.

 

[Fluff]

Glad it's sorted & thanks for the feedback

 

[steveh8204]

Is it possible to highlight the whole row in List 1 based on the results of just what is in column A?

I tried extending the range but this doesnt work.

 

[Fluff]

Select the complete range you want to highlight & add the $ signs as shown
=COUNTIF(Sheet2!A:A,$A1)+COUNTIF(Sheet3!A:A,$A1)=0

 

[steveh8204]

Thanks but every cell in rows B to G is now permanently highlighted but only the unique ones in Row A are

Thanks for your quick reply, your help is appreciated.

 

[Fluff]

What range have you highlighted?
Also which columns should the formula be looking at for the other 2 sheets?

 

[steveh8204]

The range I selected was A3:G200. The reference column on this sheet is Column A.

The formula I have is:

=COUNTIF(Sheet2!C:C,​

$​

A2)+COUNTIF(Sheet3!C:C,​

$​

A2)=0​

as it is cross referencing from Column C on sheets 2 and 3. Do you think it is related to the fact the reference columns are different?

The header on the first sheet is 2 rows high and 1 row on sheets 2 and 3, hence the range not being the first one.

Sorry my PC took ages to reboot then after a mount-image related freeze.

 

[Fluff]

In this instance your reference cell needs to be the top left cell in you applies to range.
So it should be =COUNTIF(Sheet2!C:C,$A3)+COUNTIF(Sheet3!C:C,$A3)=0

 

[steveh8204]

Ok, its a pain because the source is in that format which i paste from but i will have to figure out a workaround, maybe where it swaps it into column A after pasting.

Back to the VBA coding!

Thanks again.

 

[steveh8204]

Right, this conditional formatting has just stopped working for no reason. I've only moved a couple of things about so I can't understand why?


I never got the formatting to highlight the whole row either even though I used the references as stated.


I've changed the formatting of the cells to General, Number and Text and double checked the cell references but nothing. I've recently started importing the data from another network file but it is set up as plain text (.xlsx) so I dont understand how that can be the issue.

Would it be possible to use COUNTIF in vba to do the exact thing? I was thiinking maybe a loop that went through one sheet and checked (via COUNTIF) if it was in the other sheet and if so applied several conditions.

Any ideas please?