Excel Newbie

Zad1107a

New Member
Joined
Oct 25, 2019
Messages
7
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,135
Office Version
365
Platform
Windows
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:
Code:
=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

New Member
Joined
Oct 25, 2019
Messages
7
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:
Code:
=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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,135
Office Version
365
Platform
Windows
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

New Member
Joined
Oct 25, 2019
Messages
7
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,135
Office Version
365
Platform
Windows

Zad1107a

New Member
Joined
Oct 25, 2019
Messages
7
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,135
Office Version
365
Platform
Windows
I think I see your probelm right away:
Code:
[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:
Code:
=TODAY() - D2 <= 30
 

Zad1107a

New Member
Joined
Oct 25, 2019
Messages
7
I think I see your probelm right away:
Code:
[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:
Code:
=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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,135
Office Version
365
Platform
Windows
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.
Code:
=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.
Code:
=AND(D12>0,D12-TODAY()<=30)
 

Forum statistics

Threads
1,077,855
Messages
5,336,790
Members
399,102
Latest member
chudson1

Some videos you may like

This Week's Hot Topics

Top