VBA Excel Macro

[Shines0012]

Hi,

I am wanting to build a macro to search for a data match from one table to another that may or may not have that data. The range I am searching from is in a list of about 100k items, so you can see how tedious this could be. There is no specific number I am looking for, I am just wanting to go to the end of the data set. From there, I want to search for it in another sheet, if the value is there, I want to highlight it. If the value is not there, I want to just move on to the next row. I tried to record this as a macro but I wasn't able to get the specifics of it in the recording. Ideally, I would also like to match up the dates and if the date from the original set matches exactly, highlight it yellow. If it is another date, highlight it red but this seems like it would be another macro on it's own.

Thank you

 

[offthelip]

One of the things to be aware of when you are designing a system using VBA is trying to keep the execution time of your processes to something sensible. If you have got 100K items searching it is likely to take along time. So if the code is going to look through all the data it should be done in a way that is fast and also achieves the final result of what you are trying to do. You have stated that you want the results highlighted. However this is something which is good for appearance but I doubt it is the "Final" thing you are looking for. Even after highlighting a row you are going take some time trying to find any row that is highlighted in 100K of data. The other thing to be aware of is that VBA is very fast when doing things in memory but is very slow when doing anything on the worksheet ( i.e. reading values or changing formats) So if the result of your macro is to highlight a row or a cell. VBA will be very slow at highlighting it and also trying to find it again. A much better way is to use an additional column at the end of your data and write TRUE or FALSE in that column. This can be done with a single access to the worksheet which will be much faster than highlighting a number of rows . This means that you can process these lines very rapidly in VBA and you can also highlight the row usiing conditional formatting if you do want the rows to change color. You haven't given any details fo the layout of your workbook so it is impossible to suggest any code