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

netrixuser

Board Regular
Joined
Jan 21, 2019
Messages
77
Office Version
  1. 365
Platform
  1. Windows
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]
VBA Code:
=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:

1605521338598.png

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)
1605521411601.png


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:

VBA Code:
 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
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
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.
 
Upvote 0
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

VBA Code:
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 .......
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,224,827
Messages
6,181,197
Members
453,022
Latest member
RobertV1609

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top