Using VBA code to match a date

The short answer is: use the MATCH() worksheet function.

What makes it potentially long is that you want to match dates and dates are different from other kinds of data because they have 4 formats instead of the 2 for most other data types in Excel. I'll explain.

An Excel cell may contain a formula, like =SUM(3, 4, 5).  It will display the result, 12. In VBA we talk of the cell's "Formula" and the cell's "Value". When using Excel's MATCH function you look at the Value and when using the Find function you specify whether to search in Values or Formulas.

In Excel, a date is a number. Today's date happens to be 44422. Tomorrow will be 44423 etc. 44422.5 is 12 noon today and 44422.75 will be 6 pm today. Using enough digits you can express time with a precision of better than a 10000th of a second. When you enter the formula =NOW() the result will be such a number. You can make it visible by applying a cell format of "General". So, the cells' "Formula" is =NOW(), its "Value" may be 44422.5 and it's display may be 14/08/2021 12:00:00. That is because Excel, knowing to expect a date/time from the NOW() function applied a suitable format to it, to wit, the format specified for "Short date" in Windows Regional Settings. If you change that format to "General" or another date format the display will change accordingly. Excel's lookup functions, like VLOOKUP or MATCH, become confused and don't know whether to look for the number, the default date format created from that number or the actual display in the cell which is based on the cell's format. (Full disclosure: It's me who is confuised, not Excel. I have to try it out every time because I keep forgetting.)

So, it may be understandable that other users also become confused. Instead of entering 44422.5 in a cell they enter "14/08/2021 12:00:00" where WYSIWYG ("what you see is what you get", in case you forgot) looks like the shortest way to logic. And indeed that looks fine but disables all efforts to work with that cell as a date because Excel recognises it as a text string, not a date at all. Of course, you can't do calculations with text strings and you can't use MATCH or VLOOKUP trying to look for a date because there is none.

So, the end of the long answer is to prepare a dummy workbook that doesn't contain proprietary data but has the layout and the data types of the real one. You can use that workbook to ask questions here and expect precise answers that you can implement in your proprietary workbook.