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

Sample Workbook.xlsx

Of course I have attached it with this post. I have included the raw data in tab raw and shown the manual completion in the second tab. I have not filled in all of the cells as they are not needed, however on my real spreadsheet these there is data present in the majority of these cells. Also I was unable to input the dates in column AD as general format so I have input them as text.
Many thanks

re date issue, it can be removed either manually by selecting cell with number 1, copy, paste special operation:=multiply. The problem lies in the fact that Excel expects the first number to be month so any number exceeding twelve will result the date to be interpreted as string.

VB option

Sub test()

Dim x, i As Long, idate

With Range("ad2", Cells(Rows.Count, "ad").End(xlUp))
    x = .Value
    For i = 1 To UBound(x)
        idate = x(i, 1)
       If idate <> "" Then x(i, 1) = DateSerial(Year(idate), Month(idate), Day(idate))
    Next
    .Clear
    .Value = x
End With

End Sub

re formula: I suppose there is some mistake in it because I can not get "yes" for manually highlighted rows of your sample file.

try this:

Sub test()

Dim x, i As Long, idate, lrow As Long

Application.ScreenUpdating = 0

lrow = Cells(Rows.Count, "a").End(xlUp).Row

With Range("ad2", Cells(Rows.Count, "ad").End(xlUp))
    x = .Value
    For i = 1 To UBound(x)
        idate = x(i, 1)
        If IsDate(idate) Then x(i, 1) = CDate(idate)
    Next
    .Clear
    .Value = x
End With

Range("af2:af" & lrow) = "=if(countif(x2:ab2,"">0""),""no"",if(and(ac2>0,ad2=e2),""no"",""yes""))"

With Range("a2:ae" & lrow)
    .Select
    .FormatConditions.Delete
    .FormatConditions.Add Type:=xlExpression, Formula1:="=$AF2=""yes"""
    .FormatConditions(1).Interior.ColorIndex = 6
End With

Range("a1").Activate

Application.ScreenUpdating = 1

End Sub