I had a look at the sample you sent, it was all formatted as dd/mm/yyyy, maybe excel does that on opening (My date format is dd/mm/yyyy). I changed the code to test for mm/dd/yyyy and mm/dd/yy, tested it and all seems ok here. You may have to change some of the cells to mm/dd/yyyy or mm/dd/yy manually. That American format causes a lot of problems doesn't it.
I added .Interior.Color = vbRed to the code on front of the clear command for the dates in column A and .Interior.Color = vbYellow for column B. There's a button on sheet 2 to test the code. If it finds either or the above date formats, the cells change to red, if it finds the 555,555 number format, the cells turn yellow.
I've left it as it shows on my computer after the macro has run. Change the cell fill colours to no colour & try it. The columns C & D are in the same format for easy copy/paste when testing. Delete the .Interior.Color = vbYellow & the same for the red and delete the ' before ' before .Clear to test it clearing the cells.
Cheers
|