Solved Excel shade by current month

March 13, 2015 at 05:45:28
Specs: Windows 7
How do I shade a column according to the current month.
Along the top, I have Jan to Dec.
E.g. current month is March - so the March cloumn should be shaded & no others. When April start, only the April cloumn is shaded etc.
Tried all sorts of Today()/Now() cominations in conditional formatting but nothing works.

See More: Excel shade by current month

Report •


#1
March 13, 2015 at 06:55:22
With Jan - Dec in A1:L1, this process worked for me:

1 - Select Columns A:L
2 - Conditionally format the columns with this formula:

=MONTH(TODAY())=MONTH(A$1 & " 1, 2015")

The MONTH(A$1 & " 1, 2015") portion creates an actual Date for Excel to use when comparing the Month in each Row 1 cell to MONTH(TODAY())

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
March 13, 2015 at 07:13:40
Here is another option,
With your Column Headings in Row 1 as Text Strings,
that spell out the complete month like this:

      A         B       C      D     E     F     G
1) January  February  March  April  May  June  July

1) Select your range of cells A1:L5000
2) On the ribbon click Conditional Formatting
3) Click on New Rules, it’s near the bottom of the dialog box.
4) Click Use Formula to determine which cells to format.
5) Enter the formula:

=TEXT(TODAY(),"MMMM")=A$1

6) Click on the Format button
7) Select the Fill Tab
8) Select a pretty color
9) Click OK
10) Click OK

I would strongly advise against selecting Whole Columns.
Do you really need to CF over 12 MILLION cells??
Select a reasonable range and use that, I have used 5000
here.

MIKE

http://www.skeptic.com/


Report •

#3
March 13, 2015 at 07:55:11
✔ Best Answer
=TEXT(TODAY(),"MMMM")=A$1

The formula might have to be modified as follows since the OP said "Along the top, I have Jan to Dec." (i.e. 3 letter months)

=TEXT(TODAY(),"MMM")=A$1

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions

#4
March 13, 2015 at 08:02:36
That's why I specified:

that spell out the complete month like this

MIKE

http://www.skeptic.com/


Report •

#5
March 13, 2015 at 08:05:47
Hello,

Yes - it work perfectly, have stopped tearing my hair out.

=TEXT(TODAY(),"MMMM")=A$1

Many thanks


Report •

#6
Report •

Ask Question