Conditional Formatting based on dynamic Date

  • Thread starter Thread starter aj.valentin
  • Start date Start date
A

aj.valentin

I have an Excel file with conditional formatting (screenshot attached), if the condition is true, the entire row shall be highlighted (to Green) and have a top/bottom border.

What I really need is for the condition to format based on the following criteria:


-Highlight (Green) w/borders if the day for the previous month is greater than the 14th.
Note1: 'Previous month' is the month before the Excel file was created.
Note2: The date is in the format: Date [3/14/01]

I need to evaluate only on the days (15th thru End-of-Month) based on the previous month from whenever the file was created.

For instance:
If the file was created on March, I need the Condition based the previous month (February) for the 15th thru End-of-Month.
If the file was created on April, I need the Condition based the previous month (March) for the 15th thru End-of-Month.
If the file was created on May, I need the Condition based the previous month (April) for the 15th thru End-of-Month.
....and so on.
 
I have an Excel file with conditional formatting (screenshot attached), if the condition is true, the entire row shall be highlighted (to Green) and have a top/bottom border.



What I really need is for the condition to format based on the following criteria:





-Highlight (Green) w/borders if the day for the previous month is greater than the 14th.

Note1: 'Previous month' is the month before the Excel file was created.

Note2: The date is in the format: Date [3/14/01]



I need to evaluate only on the days (15th thru End-of-Month) based on the previous month from whenever the file was created.



For instance:

If the file was created on March, I need the Condition based the previous month (February) for the 15th thru End-of-Month.

If the file was created on April, I need the Condition based the previous month (March) for the 15th thru End-of-Month.

If the file was created on May, I need the Condition based the previous month (April) for the 15th thru End-of-Month.

...and so on.
Click to expand...
Send your file direct to me and I'll send you a similar file I did a few days ago using advanced filter (fast) and format & color each visible cell in the filtered range. This is the basic idea where your formula is in j2 and j1=""

With ActiveSheet.Range("a2").CurrentRegion
'.Select
..Offset(1).AdvancedFilter Action:=xlFilterInPlace, _
CriteriaRange:=Range("J1:J2"), Unique:=False
End With
 
Back
Top