Conditional Formatting

Bazola

New Member
Joined
Mar 14, 2018
Messages
30
Hi All

I am hoping this will be a quick one!

I've got a spreadsheet with some columns in it, at the top it says the date such as Mar-19, next column says Apr-19 and so on. I want to use CF to highlight the current month and the associated cells below column wise. So for example,
Mar-19 is cell D4, the cells I also want to be highlighted as part of it are Cells D5-D8.

Your help is appreciated :)
 

Some videos you may like

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

footoo

Well-known Member
Joined
Sep 21, 2016
Messages
3,050
Office Version
  1. 2016
Platform
  1. Windows
Select rows 4-8
CF formula : =MONTH(A$4)=MONTH(TODAY())
 
Last edited:

Bazola

New Member
Joined
Mar 14, 2018
Messages
30
Select rows 4-8
CF formula : =MONTH(A$4)=MONTH(TODAY())

Brilliant, thank you. I presume If I want to do the same for the other columns, do I need to do a seperate formula for each one or just change the "Applies to" to include the bigger selection?

Thanks
 

Bazola

New Member
Joined
Mar 14, 2018
Messages
30
When I change the "Applies to" include all the other cells with the other months + figures below, it also highlights Mar-20 as well? Instead of Just Mar-19? Any ideas?
 

footoo

Well-known Member
Joined
Sep 21, 2016
Messages
3,050
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

I presume If I want to do the same for the other columns, do I need to do a seperate formula for each one or just change the "Applies to" to include the bigger selection?
No. The "Applies to" ($4:$8 - i.e. rows 4:8) already covers all columns.
When I change the "Applies to" include all the other cells with the other months + figures below, it also highlights Mar-20 as well? Instead of Just Mar-19? Any ideas?
It is not clear what you want to do.
 

Bazola

New Member
Joined
Mar 14, 2018
Messages
30
Sorry for not explaining properly. I have a number of column with say:

March 19
Apr 19
June 19
and so on

Underneath each column, I have figures. Basically I want CF to highlight the particular column all the cells. So if its March 19, I want that entire column to be red, and the rest stay white. But I want as little formulas as possible
 

josvill2010

New Member
Joined
Jun 1, 2011
Messages
12

ADVERTISEMENT

Sorry for not explaining properly. I have a number of column with say:

March 19
Apr 19
June 19
and so on

Underneath each column, I have figures. Basically I want CF to highlight the particular column all the cells. So if its March 19, I want that entire column to be red, and the rest stay white. But I want as little formulas as possible


I have the same question. What I have done is copy the formula to the next column
 

Bazola

New Member
Joined
Mar 14, 2018
Messages
30
Sorry for not explaining properly. I have a number of column with say:

March 19
Apr 19
June 19
and so on

Underneath each column, I have figures. Basically I want CF to highlight the particular column all the cells. So if its March 19, I want that entire column to be red, and the rest stay white. But I want as little formulas as possible

Ok, so I've modified the formula to point to Mar-19, and I've also modified the Applies to =$C$4:$O$8, this is all the columns above all the way up to March 20. However, now March 20 also gets highlighted in a different colour like March 19 at the same time. If I change the time on my pc to april, it highlights april and drops march which is good. But when it sees March 19 and March 20, it matches them both for some reason?
 

footoo

Well-known Member
Joined
Sep 21, 2016
Messages
3,050
Office Version
  1. 2016
Platform
  1. Windows
=month(c$4)&year(c$4)=month(today())&year(today())
 

Watch MrExcel Video

Forum statistics

Threads
1,109,485
Messages
5,529,143
Members
409,851
Latest member
Ingar
Top