Convert General Format to Date, Nested IF Function, If Yes then highlight row

Hi All,

Any help with this will be much appreciated. So I have a fairly manual process that I am trying to automate with a Macro. The solution needs to work on spreadsheets with differing numbers of rows but always exactly the same columns. All columns have headings in Row 1 so the data is contained below this and the spreadsheet width is up to AE.

Step 1
Column AD2 contains dates that are Formatted by General not Date. This causes me an issue as there is a formula in the next step that will not work unless these values are converted. I have tried highlighting the whole column, selecting Data, Text to Columns then Finish and when I record this into a macro it only works around 50% of the time. Any ideas on how this can be written in VBA?

Step 2
Next I need to run the following Formula for all rows where there is data in column A. This will always be a different number of rows and column A is the one of the only rows where there will definitely be data in every cell.

=IF(COUNTIF(X2:AB2,">0"),"no",IF(AND(AC2>0,AD2=E2),"no","yes"))

Step 3
If the formula = yes then I need the whole row to be highlighted yellow

Any help is much appreciated and if there is any further information I can provide please let me know.

Thanks

Woody