Excel/VBA Conditional Formatting not working for a result of an Index/Match lookup

[netrixuser]

Good morning.
To set the scene:
I have the following code copied down Column AF in the worksheet "Accsys Report" [Column AF isformatted as Date]

=IFNA(IF(V2="yes",INDEX('VoxSmart Report'!I:I,MATCH(B2,'VoxSmart Report'!Y:Y,0)),"Not Recorded For WhatsApp"),"Problem!")

Column I in the Voxsmart Report worksheet contains dates
An example of the results of the Index/Match are:

One of the Dates above is more than 30 days "old" so I have a Conditional Format set to capture this: (it also applies to dates in Column AG, as per the screenshot)

My problem is that the format does not take place.
If I copy the date "30/08/2020 02:53:52" and "paste values" over the top it still does not work but if I just replace "30/08/2020 02:53:52" by manually typing "30th August 2020" the Conditional Format does change the cell background colour to orange.

I should point out that Conditional Formatting used to work but I cannot find out what broke it - there are no other Conditional Format rules in the spreadsheet and the conditional format code is virtually the last code in the Sub.

This is the Conditional Format Code - which is working in essence as it changes the background colour if I manually enter a date older that 30 days:

 Range("AF2:AG2").Select
        
        Range(Selection, Selection.End(xlDown)).Select
        Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, _
        Formula1:="=NOW()-30"
        Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
            With Selection.FormatConditions(1).Interior
              .PatternColorIndex = xlAutomatic
              .Color = 49407
              .TintAndShade = 0
            End With
        
        Selection.FormatConditions(1).StopIfTrue = False

Many thanks in advance for any pointers

Regards
Netrixuser

 

[ekrause]

From a troubleshooting perspective can you change the location of the day and month in your Conditionally Formatted cells? I was trying to solve another way but changing 30/08/2020 02:53:52 to 8/30/2020 2:53:52 AM corrected the Conditional Formatting for me without any other change.

 

[netrixuser]

Hi ekrause, thank you for your response. I agree, if I input a date manually in the cells that are Conditionally Formatted, or change the date manually in the Index/Match referenced cell the Formatting does work - so the issue is the original format of the date in the referenced cell.

I sort of proved this by using the following code to format any cell that isn't blank, using the same =now() -30 formula

Sub ConditionalFormatAlternative()

        Worksheets("VoxSmart Report").Select
        Range("I2").Select
        Do Until ActiveCell = ""
            If ActiveCell <= Now() - 30 Then
                ActiveCell.Font.Bold = True
                ActiveCell.Interior.Color = 44
                
            Else
                ActiveCell.Font.Bold = False
            End If
        ActiveCell.Offset(1).Select
            
        Loop


 End Sub

I ran this directly on the Date column [column I] of the raw report and it just skips over every entry without changing the Font to Bold etc.
I have tried using Format Cells to change the Format to Date but it makes no difference - I suppose once I crack how to turn the Date in column I to a date it will work !
Frustratingly if I manually type the date and time "30/08/2020 02:53:52" in the cell and run the code above it does change the Font to Bold
My next step is to try and remove the "02:53:52" portion from the Date column .......

 

[ekrause]

I would first see if you could adjust your source data to output the column as month/day/year time because then you don't need to worry about any additional manipulation.

I tinkered around some more and you can solve for it using Conditional Formatting on the Home Ribbon with the following Formula, where E12 is the start of your data range =DATEVALUE(MID(E12,FIND("/",E12)+1,FIND("/",E12,FIND("/",E12)))&MID(E12,1,FIND("/",E12))&RIGHT(E12,LEN(E12)-FIND("/",E12,FIND("/",E12)+1)))<NOW()-30

I tried to keep it dynamic in case you don't have preceding 0s for each month and single digit days e.g. Jan 1 2021 would read 01/01/2021 versus 1/1/2021. This also drops the time value if that's important to you.

Let me know if that solution works.