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

bearcub

Well-known Member
Joined
May 18, 2005
Messages
701
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

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,974
Members
448,537
Latest member
Et_Cetera

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
Back
Top