Comparing 2 lists and highlighting cells.

steveh8204

Board Regular
Joined
Aug 20, 2018
Messages
143
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.
 
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.
 
Upvote 0

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
Glad it's sorted & thanks for the feedback
 
Upvote 0
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.
 
Upvote 0
Select the complete range you want to highlight & add the $ signs as shown
=COUNTIF(Sheet2!A:A,$A1)+COUNTIF(Sheet3!A:A,$A1)=0
 
Upvote 0
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.
 
Upvote 0
What range have you highlighted?
Also which columns should the formula be looking at for the other 2 sheets?
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,213,487
Messages
6,113,943
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top