Conditional Formatting of entire Column based on Month Header

[wanji]

I want to use conditional formatting to draw a box around a group of columns (in sets of 4) if they are within the current month. In my example I'd like to draw a box around C1:F7 because they all have a header that is within the month of April. In May, I'd want the box to move to G1:G7 because they are within the month of May. All grouping will be in columns of 4 so should be able to have a formula.

 

[wanji]

The finished result would look similar to this. I manually drew the box on this one. I'd want the box drawn around the columns with April because that's the month we are currently in.

 

[wanji]

If I chose highlighting those 4 date columns instead of outlining would that make it easier? I just didn't want to highlight over the colored cell but I guess I could change that to text in those already colored cells. I haven't had any replies on my original question so maybe it's not doable. Thought that maybe another option might get me an answer.

 

[6StringJazzer]

Is the number of rows fixed? I am going to assume yes.

It's doable. You need four separate rules. I don't know how many columns you are using so ? is the last column.

Rule in C1:?1
=MONTH(C$2)=MONTH(TODAY())
Add a border on the top

Rule in C1:?7
=AND(MONTH(C$2)=MONTH(TODAY()),MONTH(B$2)<>MONTH(TODAY()))
Add a border on the left

Rule in C1:?7
=AND(MONTH(C$2)=MONTH(TODAY()),MONTH(D$2)<>MONTH(TODAY()))
Add a border on the right

Rule in C7:?7
=MONTH(C$2)=MONTH(TODAY())
Add a border on the bottom

I have not tested this but would be happy to if you have a way to share your file.

 

[Eric W]

Try:

Book2
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1			April				May				June
2			4/1/2021	4/1/2021	4/1/2021	4/1/2021	5/1/2020	5/1/2020	5/1/2020	5/1/2020	6/1/2020	6/2/2020	6/3/2020	6/4/2020
3	Workplace Intelligence	Needs Assessment
4		Workplace Strategy
5	Transaction Management	Site Identification
6		Evaluation of Alternatives
7		Offer and Lease Negotiation
8
Sheet13

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B1:N7	Expression	=(EOMONTH(B$2,0)=EOMONTH(TODAY(),0))*(EOMONTH(B$2,0)<>EOMONTH(C$2,0))	text	NO
B1:N7	Expression	=(EOMONTH(B$2,0)=EOMONTH(TODAY(),0))*(EOMONTH(B$2,0)<>EOMONTH(A$2,0))	text	NO
1:1,8:8	Expression	=EOMONTH(A$2,0)=EOMONTH(TODAY(),0)	text	NO

XL2BB didn't pick up the specific formatting. The first rule would be to select the right border, the second rule would be to select the left border, and the last rule would be to select the top border. The top border of row 8 is the same as the bottom border of row 7, so we can save one rule. Also, using EOMONTH instead of just MONTH allows us to check if the year matches as well.