Excel Newbie

[Zad1107a]

Hi everyone,
Im trying to go above and beyond by making an inventory sheet for work. I want excel to highlight the expiration dates of items that will expire in less than 30 days but I have no idea to write the formula(s). If I write out what I want to have happen it looks something like this: IF(Date-Today)=<30 then highlight.

Thanks in advance!!

 

[Joe4]

Welcome to the Board!

You can use Conditional Formatting to do that.
If the date was in cell A1, then then select that cell, go to Conditional Formatting, and enter the following Conditional Formatting formula:

=TODAY()-A1 <= 30

and choose your yellow formatting option.

If you have a whole column of dates you want to apply this to, select the whole column first, and then write the CF formula as it pertains to the top cell in your selected column. Excel will adjust it for all the other rows.

 

[Zad1107a]

Welcome to the Board!

You can use Conditional Formatting to do that.
If the date was in cell A1, then then select that cell, go to Conditional Formatting, and enter the following Conditional Formatting formula:

=TODAY()-A1 <= 30

and choose your yellow formatting option.

If you have a whole column of dates you want to apply this to, select the whole column first, and then write the CF formula as it pertains to the top cell in your selected column. Excel will adjust it for all the other rows.

Thanks for the reply!
When I tried to enter this under CF then it highlighted the whole column. I tried just one cell as well but no luck :/

 

[Joe4]

This shows you how to use the Formula option in Conditional Formatting.
See if that clarifies things for you. If not, please list out the exact steps you are doing.

 

[Zad1107a]

This shows you how to use the Formula option in Conditional Formatting.
See if that clarifies things for you. If not, please list out the exact steps you are doing.

If you included a link, it doesn't look like it sent. I selected Column D then clicked Conditional Formatting under Styles tab. Clicked "create new rule" followed by "use formula to determine which cells to format." Once that opened I pasted the formula and changed A1 to D1. Clicked format and selected my color. Exact formula used was =TODAY()-D1 <= 30. For the single cell trial I did the same thing but changed D1 to D3. If it helps, I am using 2010 not 2016.

Thanks again

 

[Joe4]

Sorry, here is the link: https://www.ablebits.com/office-add...nditional-formatting-formulas/comment-page-6/

Can you do the following?
1. Tell us exact values are in cells D1, D2, and D3.
2. Turn on your Macro Recorder, and try your steps again. Then stop the Macro Recorder, go into the VB Editor, and copy and paste your VBA code here for us to see.

This will show us exactly how you are setting it up, and we should be able to identify any issues with that part.

 

[Zad1107a]

D1 is the title cell "Expiration." Cell is merged with E and F.
D2 Blank
D3 05/31/2020
First applicable expiration comes in at D11 11/1/19

Columns("D:D").Select
Range("D2").Activate
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=TODAY()-D1 <= 30"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.Pattern = xlPatternLinearGradient
.Gradient.Degree = 90
.Gradient.ColorStops.Clear
End With
With Selection.FormatConditions(1).Interior.Gradient.ColorStops.Add(0)
.ThemeColor = xlThemeColorDark1
.TintAndShade = 0
End With
With Selection.FormatConditions(1).Interior.Gradient.ColorStops.Add(1)
.Color = 49407
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
Range("H15").Select
End Sub

 

[Joe4]

I think I see your probelm right away:

[COLOR=#333333]Range("[/COLOR][COLOR=#ff0000]D2"[/COLOR][COLOR=#333333]).Activate[/COLOR]
[COLOR=#333333]Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _[/COLOR]
[COLOR=#333333]"=TODAY()-[/COLOR][COLOR=#ff0000]D1[/COLOR][COLOR=#333333] <= 30"[/COLOR]

It looks like you are selecting all of column D, but then selecting a specific cell (D2) to apply your Conditional Formatting, and then you are telling it to use D1 in its logic!
You do NOT want to select any single cell between selecting your column and enter the Conditonal Formatting formula!

If you do select any range that is NOT the entire column, you need to then write your formula as it applies to the first cell in your selected range.
So, if you choose D2:D10, you would write the formula:

=TODAY() - D2 <= 30

 

[Zad1107a]

I think I see your probelm right away:

[COLOR=#333333]Range("[/COLOR][COLOR=#ff0000]D2"[/COLOR][COLOR=#333333]).Activate[/COLOR]
[COLOR=#333333]Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _[/COLOR]
[COLOR=#333333]"=TODAY()-[/COLOR][COLOR=#ff0000]D1[/COLOR][COLOR=#333333] <= 30"[/COLOR]

It looks like you are selecting all of column D, but then selecting a specific cell (D2) to apply your Conditional Formatting, and then you are telling it to use D1 in its logic!
You do NOT want to select any single cell between selecting your column and enter the Conditonal Formatting formula!

If you do select any range that is NOT the entire column, you need to then write your formula as it applies to the first cell in your selected range.
So, if you choose D2:D10, you would write the formula:

=TODAY() - D2 <= 30

I see that error as well but for whatever reason, it defaults to D2 and I dont see an option to change that. I went ahead and tried again by selecting just D12 (Value is 01/31/2020) and had the same results.

Range("D12").Select
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=TODAY()-D12 <= 30"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.Pattern = xlPatternLinearGradient
.Gradient.Degree = 90
.Gradient.ColorStops.Clear
End With
With Selection.FormatConditions(1).Interior.Gradient.ColorStops.Add(0)
.ThemeColor = xlThemeColorDark1
.TintAndShade = 0
End With
With Selection.FormatConditions(1).Interior.Gradient.ColorStops.Add(1)
.Color = 49407
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
Range("I23").Select

 

[Joe4]

Ah, I think I have it backwards. If these are future dates, we should be subtracting the current date from the existing date (sometimes it really helps to see the examples!).
So try switching those values around, i.e.

=D12-TODAY()<=30

Note that if you may have some blank values in column D that you do NOT want highlighted, we will need to add another check, i.e.

=AND(D12>0,D12-TODAY()<=30)