Highlighting row using Condition Formatting if Date is less than month date

bearcub

Well-known Member
Joined
May 18, 2005
Messages
629
Office Version
  1. 365
  2. 2013
  3. 2010
  4. 2007
Platform
  1. Windows
I have a schedule which contains draws we need to pay our new reps.

In row 1, I have the following formula in K1 using conditional formatting for the current the commission month : EOmonth(Now(),1).
If it is false, nothing is shown (L1-O1)

In row 2, right below the formula I have a month that the draw is due to paid.



1618272935479.png


In this example, I need column K to be highlighted. All the other columns are not highlighted in yellow, only column K (the month name that is visible - I have conditional formatting in row 1 which shows the month that we are paying commissions).

Next month, K becomes un-highlighted but column L becomes highlighted instead. I would like to make the column highlight per above.

Is there a formula that I could use which will do this?

Thank you in advance for your help,

Michael
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,461
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Assuming the dates are in Row 1 as dates, not text, enter 12/1/2020 formatted as "mmm-yy" in A1:
Cell Formulas
RangeFormula
B1:M1B1=DATE(YEAR($A1)+1,MONTH(EOMONTH(A1,0)+1),1)
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A2:M100Expression=DATE(YEAR(TODAY()),MONTH(TODAY()),1)=A$1textNO
 

6StringJazzer

Well-known Member
Joined
Jan 27, 2010
Messages
865
Office Version
  1. 365
Platform
  1. Windows
Apply this formula rule to your data, assuming your data starts in column G as shown:

Excel Formula:
=G$2=EOmonth(Now(),1)

However, I am not sure how to exclude those orange rows where you have not applied the yellow.
 

bearcub

Well-known Member
Joined
May 18, 2005
Messages
629
Office Version
  1. 365
  2. 2013
  3. 2010
  4. 2007
Platform
  1. Windows
Thank you, it works!

I played with the formula a little bit and I think I found a solution to turn off the yellow color if the cell is "":

AND(K3<>"",K$2=EOMONTH(NOW(),0))

1618287460604.png


1618287486264.png
 

6StringJazzer

Well-known Member
Joined
Jan 27, 2010
Messages
865
Office Version
  1. 365
Platform
  1. Windows
What is the Applied To range you are using for that rule? If your formula refers to column K then it will work only starting in K. If you want to apply it to all columns of data you have to refer to the leftmost column.
 

bearcub

Well-known Member
Joined
May 18, 2005
Messages
629
Office Version
  1. 365
  2. 2013
  3. 2010
  4. 2007
Platform
  1. Windows
The Leftmost column for now is column K. I have already paid the prior months. The range I highlighted was K:O when I entered the formula. The formula works as I also copied it into a master file that I have for new hires. Understood about the leftmost range. I didn't clarify where I wanted to start. My intend was to start in Column K. I have that same formula in row where it will display the current month and hide the others.
 

Forum statistics

Threads
1,137,125
Messages
5,679,751
Members
419,855
Latest member
Eddier32

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top