Check cell format is Date or Time

Hello,

I am not an expert in VBA and I would appreciate some help with a problem I am trying to solve.

I have a table where I have information of various products (A1:C14). I would like to insert a VBA code to automatically check if the information inserted in column B has the Date format (dd-mm-yyyy) and if the information inserted in column C has the Time format (hh:mm:ss), if the format is not the same the cell's fill colour should change to red.

I adapted a code from the net, but the result is not the ideal, the blank cells also change the background to red.

Any suggestions would be greatly appreciated

Thank you in advanced.

Hi mehmetcik,

The ideal solution is to have the data checked as it is typed in


If the cell's fill colour change to red, the end users know that they made a mistake with the format of the inserted information.

Best regards,

Hi mehmetcik,

The vba seem to work fine in the "Date" format, but in the column of the "Time" format if I insert a text it gives me an error.

Also, when I delete all the information of the cells they colour keep in red.

Thank you for your help,

Hi mehmetcik,

When I insert information in the "Date" column is tranformed to the hour format.

In the column of the "Time" format if I insert a text (example: test) the cells do not changed to red.

It is possible , when we delete all the information of the columns, to the red colour disappear ?

Kind regards,

Hi mehmetcik,

I have tryed to delete the conditional formatting before testing the new code but is not working.

Now the excel,starts to slow down and block, sometimes I select a row and it goes to another cell.

Hello mehmetcik,

It is possible only to change the following code in order to ignore blank cells? If the cell is blank, the background colour not change to red.

HTML Code: 

Thank you for all your time and help.

Hi mehmetcik,

I am sorry but when I tryed to insert information, the excel starts to slow down and block....for example when I insert information in cell B2 the excel blocks and goes to B3 or B4...something strange is happening.

Instead of having a VBA code I am going try to insert the usual conditional formatting in the columns, the only problem Is that when the users do the "copy/paste" action, do not works.

Best regards,

Hi mehmetcik,

Yes I am going to use the Data Validation feature.
This was my first ideia, but when I did the test to copy and paste I realized that it doesn't work (the message/alert does not appear in the scenario the user copy data with another format). Data validation is only checked if you write data manually (by user action). After reading some information on the net, I thougth the only way to overcome this problem was to create a vba code but I am not able to do it.

Thank you for all the help.