conditional formatting: highlight table row where cell month (text) matches current month?

[BSWJON]

As titled, could use help on achieving conditional formatting which highlights a table row if the month specified in column C matches the current month.

Could also use a second formula for highlighting in a different colour therows with a month which is coming up next (so if the current month is July, August dates would be highlighted).

As the table is for recurring annual checks, the data in column C is formatted as general (so text), not date.

I have tried this formula:

=TEXT($B3,”mmyy”)=TEXT(TODAY(),”mmyy”)

To no avail.

I've tried entering a July date in cell B3 as a date to see if that helps, which it doesn't!

I would post XL2BB code however it's gone and disappeared from my ribbon.

Table data starts in cell B3:

Many thanks as always.

 

[BSWJON]

If C3 is formatted as "General", and returns "January", then it is definitely a text entry (and not a date one).

The Conditional Formatting formula you posted shows:

=$C3="T1"

that is checking to see if the value in cell C3 is equal to the text value of "T1", which I don't think will ever be true (I am not sure where you are getting the "T1" from).

If you simply want to check to see if the value in C3 is equal to the current month, then you would use

=$C3=TEXT(TODAY(),"mmmm")

I think that T1 reference came from an online formula I found perhaps.

In any case I tried your formula and it didn't work

 

[Joe4]

In any case I tried your formula and it didn't work

It should, if you implemented it correctly.

I set up a simple example resembling yours, complete with a table, data, and conditional formatting.
Try running this VBA code on any blank sheet, and you should see it will highlight the "July" row:

Sub MyTestMacro()

    Range("B2") = "Ref"
    Range("C2") = "Month"
    Range("D2") = "Regulator"
    Range("B3") = 1
    Range("C3") = "January"
    Range("B4") = 2
    Range("C4") = "February"
    Range("B5") = 3
    Range("C5") = "March"
    Range("B6") = 4
    Range("C6") = "April"
    Range("B7") = 5
    Range("C7") = "May"
    Range("B8") = 6
    Range("C8") = "June"
    Range("B9") = 7
    Range("C9") = "July"
    Range("B10") = 8
    Range("C10") = "August"
    Range("B2:D10").Select
    Application.CutCopyMode = False
    ActiveSheet.ListObjects.Add(xlSrcRange, Range("$B$2:$D$10"), , xlYes).Name = _
        "TableTest"
    Range("TableTest").Select
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=$C3=TEXT(TODAY(),""mmmm"")"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 65535
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
   
End Sub

This VBA just sets up the data table, and creates the Conditional Formatting like I instructed (you can see my formula in the VBA code).

 

[BSWJON]

Hi Joe,

Thanks for this. The VBA code worked perfectly.

I copied the code into the conditional formatting menu though, and initially it displayed an error message, however I removed the additional "" by mmmm and it now works fine.

Thank you.

 

[Joe4]

Hi Joe,

Thanks for this. The VBA code worked perfectly.

I copied the code into the conditional formatting menu though, and initially it displayed an error message, however I removed the additional "" by mmmm and it now works fine.

Thank you.

When typing the formula directly into the Conditional Formatting formula section, you do NOT need to double-up the double-quotes, i.e.

""mmmm""

You would just type it like:

"mmmm"

You only use the doubled-up double-quotes when it is being entered into VBA, because in VBA double-quotes are treated as text qualifiers.
So if you want literal double-quotes as part of the formula, you need to double them up.