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.

 

[Joe4]

Are you sure that your references are correct?
Your formula looks like it is referencing cell B3, but it looks like your dates are in column C, not column B.

What does this formula return?
=TYPE(B3)

 

[BSWJON]

Hi Joe,

The help I found online stated that the reference in the formula is for the first cell in the table, not necessarily the date column.

The formula returns 1.

thanks for your help

Jon

 

[Joe4]

The help I found online stated that the reference in the formula is for the first cell in the table, not necessarily the date column.

I think that is only if your are referring to the fields by the header/field name.
But you aren't. You are using a range reference in your formula. So if you are going to use the range reference, you have to use the correct one.

 

[BSWJON]

I think that is only if your are referring to the fields by the header/field name.
But you aren't. You are using a range reference in your formula. So if you are going to use the range reference, you have to use the correct one.

Thanks Joe

Could you suggest the changes I need to make to the formula?

I have tried changing the C3 to "table 1", "Month", changing mmyy to mmmm, nothing has worked so far.

Please excuse my ignorance...

 

[Joe4]

It important for us to accurately determine what exactly is in cell C3.
If you change the format of the entry in cell "C3" to "General", what value does it show?

 

[Peter_SSs]

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

See if this resolves that issue: XL2BB Disappears when Excel is Reopened

 

[BSWJON]

It important for us to accurately determine what exactly is in cell C3.
If you change the format of the entry in cell "C3" to "General", what value does it show?

Thanks for this. C3 is formatted as general.

Copy of Annual Regulatory Returns.xlsm
	B	C	D	E
2	Ref	Month	Regulator	Return/Task
3	1	January	SEPA	SPRI Information
Annual Returns-Considerations

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B3:AF13	Expression	=$C3="T1"	text	NO

Thanks Peter, your fix worked a treat.

 

[Peter_SSs]

Thanks Peter, your fix worked a treat.

Cheers.

 

[Joe4]

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")