Highlight entire row if cells in column contain last, this, or next month.

[asabur6]

Peace,
I'm sure this is easy. I want to use a conditional formatting formula to highlight the entire row if any cell in column B contain last, this, or next month. I probably will need to use 3 different conditional formatting formulas (one for each time frame).

Thanks

[alansidman]

Capture.JPG
Here is a picture of the conditional formatting

[asabur6]

Thanks, but that doesnt apply to the entire row. It only highlights the cells containing the specified time frame.

[alansidman]

Probably need to do this with VBA. Are you comfortable with a VBA solution?

[alansidman]

ok. Here is a vba solution

Please Login or Register  to view this content.

[asabur6]

Thanks, if thats the only way to go. I dont know how to create/edit the code, but I pasted and it worked, however, (I didnt think about this)...can you specify the entire row within the table, not across the whole sheet?

[LeoSkywalker]

Use this:

Conditional formatting - Manage rules...
New 3 new rules - Use a formula to determine which cell to format:
The formulas are:
Last month

Please Login or Register  to view this content.

This month

Please Login or Register  to view this content.

Next month

Please Login or Register  to view this content.

image2.JPG
You can make "Applies to" as big as you need.


If you prefer the same format for all three options, just need one rule:

Please Login or Register  to view this content.

[asabur6]

Thanks LeoSkywalker, works perfectly! and no VBA! I knew it could be done. Do you guys use a program to input what you want and it outputs the correct formula, or do you just know the code? Is there a list of codes?

[AliGW]

We work it out from what we have learned already!!!

https://support.office.com/en-us/art...1-63f26a86c0eb

[alansidman]

Lots of trying different things and reading lots of threads in forums. Watching You Tube videos.

[LeoSkywalker]

Asabur,

As Ali and Alan said, there are a lot of resources to learn, we never stop learning, we come here and help one another, several times we come up with a solution for a problem, and then someone else comes with a better one, we learn from there, too.

Don't forget to mark threads as "Solved" if your problem has been solved.
If an answer helped you, show your appreciation by clicking * Add Reputation at the bottom left corner of the message.

[asabur6]

Hi LeoSkywalker,
This formula did work until I had to enter 2/21/2020. How do I make sure it only highlights last month (including Dec of previous year), this month, and next month (including Jan of next year). I only want bills that are past due, due currently, and due next month, a rolling 12-month period. If that helps. Thanks

[AliGW]

Try this:

=AND($B2<=EDATE(TODAY(),1),$B2>=(EDATE(TODAY(),-1)

or this:

=AND($B2<=EOMONTH(TODAY(),1),$B2>=EOMONTH(TODAY(),-2)+1)

[asabur6]

• =AND($B2<=EDATE(TODAY(),1),$B2>=(EDATE(TODAY(),-1) is missing parenthesis. I cant figure out where.
• =AND($B2<=EOMONTH(TODAY(),1),$B2>=EOMONTH(TODAY(),-2)+1) returns nothing.Adia excel.JPG

[AliGW]

The first one needs an extra bracket at the end. If you need further help, please update the actual spreadsheet - I cannot troubleshoot a picture.

[asabur6]

It still says missing parenthesis. Im sorry, I knew that I could upload a file since Ive done it before, but I was moving too fast and couldnt find how to. Here it is now.

Thanks

[Bo_Ry]

Please try CF fromula applies to A5:F20

=TEXT(TODAY(),"myy")=TEXT($D5,"myy") > this month

=TEXT(EDATE(TODAY(),-1),"myy")=TEXT($D5,"myy") > last month

=TEXT(EDATE(TODAY(),1),"myy")=TEXT($D5,"myy") > next month


There is no 31-Feb D7 is text not date.

Your others formula should refer to $D5, not $B2

[asabur6]

@Bo_Ry
I must be doing something wrong (see pic). Its not returning anything. I have each formula applied to A5:F20 and are referring to D5 as you said. Are you able to edit the conditional formatting for me, in the file I attached?
excel snippet.jpg

[Bo_Ry]

The fomula is just this. not include the "> this month"

=TEXT(TODAY(),"myy")=TEXT($D5,"myy")
=TEXT(EDATE(TODAY(),-1),"myy")=TEXT($D5,"myy")

Please download the file from Post#17 to see

[asabur6]

@Bo_Ry
Oh I'm sorry, didnt see the download before. Thank you so much, I got them all now!