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

BSWJON

Board Regular
Joined
Mar 24, 2014
Messages
101
Office Version
  1. 365
Platform
  1. Windows
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:

1626075584674.png


Many thanks as always.
 

BSWJON

Board Regular
Joined
Mar 24, 2014
Messages
101
Office Version
  1. 365
Platform
  1. Windows
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:
Excel Formula:
=$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
Excel Formula:
=$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 :(
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
59,079
Office Version
  1. 365
Platform
  1. Windows
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:
VBA Code:
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

Board Regular
Joined
Mar 24, 2014
Messages
101
Office Version
  1. 365
Platform
  1. Windows
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.
 

Attachments

  • 1627024365910.png
    1627024365910.png
    24.1 KB · Views: 3

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
59,079
Office Version
  1. 365
Platform
  1. Windows
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.
Excel Formula:
""mmmm""
You would just type it like:
Excel Formula:
"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.
 

Forum statistics

Threads
1,141,704
Messages
5,707,970
Members
421,539
Latest member
zuniBM

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
Top