Highlight Rows which are NOT found.

[theeachi]

Hi all, I am very new to VBA and struggling to make sense of it all!!

I am trying to help my wife with a spreadsheet she uses a lot. Basically I need to find a row in sheet 1 (Tracking) where Column B is the same in both and where column G in sheet 2 (BL) matches column K in sheet 1, and if so, update the data in sheet 1 column W with the data from sheet 2 column I. Now I have already had help with that bit and it works a treat Thank you....

Now I am trying to highlight any rows in sheet 2 which do not match....

I have tried and it works on my test but when I run it on the actual spreadsheet (1 sheet has 8000 rows and one has 5000 rows) it takes forever and then crashes Excel! The OverwriteMatchedData function works fine its just the HighlightNotFound bit that breaks it, I was wondering if they can be combined?

Can anyone help?? It would be greatly appreciated. My code is below:

Please Login or Register  to view this content.

[mehmetcik]

Sorry.

I hate to say it but your approach is all wrong.

Give me a few minutes and I will show you.

Just trying to get my head around your requirement.

What does this mean? I need to find a row in sheet 1 (Tracking) where Column B is the same in both.

[theeachi]

I suspected as much, I think I have a lot to learn.

"I need to find a row in sheet 1 (Tracking) where Column B is the same in both." just means Column B needs to match in both sheets, sorry I didn't explain it very well.

thank you

[mehmetcik]

Ok I have it. Please wait.

So we are replacing "Manifest" with the Processed Date?

[mehmetcik]

Nearly there. Formula for AI2 on Tracker then fill down. =IF(ISNA(MATCH(B2,BL!$B$1:$B$20,0)),"Manifest",IF(K2<>INDIRECT("BL!G"&MATCH(B2,BL!$B$1:$B$20,0)),"Manifest",TEXT(INDIRECT("BL!I"&MATCH(B2,BL!$B$1:$B$20,0)),"dd/mm/yyyy")))

That formula works.

So.

Find last row in Tracker.

Insert this formula in Column AI.

Copy Paste Formula in Column W

Delete Column AI.


There are no Loops. So this should be fast.

Please Login or Register  to view this content.

[bakerman2]

Did you try to adapt Jindon's code from your previous thread ?

Please Login or Register  to view this content.

[Pepe Le Mokko]

Administrative Note:

Welcome to the forum.

We would very much like to help you with your query, however the thread title does not really convey what your request is about.

Please take a moment to amend your thread title. Make sure that the title properly explains your request. Your title should be explicit and not be generic (this includes function names used without an indication of what you are trying to achieve).

Please see Forum Rule #1 about proper thread titles and adjust accordingly. To edit the thread title, open the original post to edit and then click on Go Advanced (bottom right) to access the area where you can edit your title.

I changed it for you this time using your own description

[theeachi]

I am really sorry, I didn't realise.

Thank you for pointing it our, I have changed it now and I will be more careful in future.

thanks again

[theeachi]

Ok I have it. Please wait.

So we are replacing "Manifest" with the Processed Date?

Good morning, yes but that bit OverwriteMatchedData() works great and its quick, it is the highlighting of the rows HighlightNotFound() that doesn't work. It works on small rows of data but when it gets over 5000 rows it causes Excel to not repsond.

[theeachi]

Did you try to adapt Jindon's code from your previous thread ?

Please Login or Register  to view this content.

Good morning, I did but it didn't overwrite with the correct date. It found matching rows and overwrote the date but instead of using the date from the matched row it used the date from the corresponding row. So for example if Row 66 in sheet 2 matched Row 10 in sheet 1, it found it but overwrote it with the date from Row 10 in sheet 2 and not Row 66 in sheet 2. I couldn't work out how to change it. I hope that makes sense??

thank you

[bakerman2]

I just tried all 3 codes (mine, jindon's & mehmetcik's) in the example file you posted in your 1st post and they all gave me same result so no, it makes no sense to me.

[theeachi]

I just tried all 3 codes (mine, jindon's & mehmetcik's) in the example file you posted in your 1st post and they all gave me same result so no, it makes no sense to me.

Hi bakerman2, it is your code that I am using and it works perfectly (thanks again), I now just need to add something which will highlight the rows in sheet 2 that are not matched.

[mehmetcik]

Bakersmans code:

Please Login or Register  to view this content.

Successfully Highlights Rows that do not math on Sheet BL.

So I assume the issue is the Tracking Sheet.

The same or similar method would work but conditional formatting would be neater and faster.

Select all cell select conditional formatting, select use formula: =AND(ROW()>1,$W1 ="Manifest")

[jindon]

I didn't test my code carefully, so try this one

Please Login or Register  to view this content.