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

#### bearcub

##### Well-known Member
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.

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?

Michael

### Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

#### JoeMo

##### MrExcel MVP
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
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
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))

#### 6StringJazzer

##### Well-known Member
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
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.

Replies
13
Views
604
Replies
3
Views
613
Replies
6
Views
143
Replies
5
Views
333
Replies
3
Views
351

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,152,392
Messages
5,769,807
Members
425,573
Latest member
AmroK

### 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.

### Which adblocker are you using?

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

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