fill cell with color based on cell value

[k1dr0ck]

hi,

i have dates on (b2:z40) how do i automatically fill the cells with color (yellow or light green) which has the same month-year as the current month-year everytime i open the file

example:
all the cells of (b2:z40)with month-year of june 2016 will be color filled since the current month-year is june 2016

how do it also auto update when july 2016 comes and the cells that will be color filled will now be the cells with the month-year of july 2016 value
the previous color filled for the previous month-year will be removed

[FlameRetired]

Use Conditional Formatting.

Select B2:Z40.

Click Home / Conditional Formatting / New rule.

Choose Use formula to determine which cells to format.

Then in the formula box this formula

=AND(YEAR(TODAY())=YEAR(B2),MONTH(TODAY())=MONTH(B2))

Click the Format button / click the Fill tab and select the color you wish. Click OK all the way out.

[k1dr0ck]

thank you works great

[FlameRetired]

Thanks for the feedback and rep. :

Had an afterthought.

This formula is simpler and better IMPO.

=AND(B2>EOMONTH(TODAY(),-1),B2<=EOMONTH(TODAY(),0))