expiry date counting down

[xueyun]

Hi,

A spreadsheet was created to log down the expiry date of products. I would
like to format the cells such that 14 days before the expirey date is
reached, the cells will turn red.

Please advise.

Thank you


Date: 24-Jun-09


Exp Date In Out Balance
1-Aug-10 6 0 6
1-Aug-10 0 1 5

 

[Per Jessen]

Hi

With expiry dates in A2 and down, select theese cells and goto Format >
Conditional formatting > Condition1: Formula is > =A2-14<=TODAY()

Click Format and format as needed > OK

Hopes this helps

 

[Jacob Skaria]

Select cell/range. From menu Format>Conditional Formatting
Select the 'Formula Is' option. Enter this formula in the box to the right
=DATEDIF(A1,TODAY(),"d")<14
Click the Format button Fill>Select the desired color>Click OK.

If this post helps click Yes

 

[Jacob Skaria]

If you are looking for a macro try the below. Adjust the range to
suit...Right click the sheet tab>View Code and paste the below code...

Private Sub Worksheet_Change(ByVal Target As Range)
Dim myRange As Range
Application.EnableEvents = False
Set myRange = Range("A2:A10")
If Not Application.Intersect(Target, myRange) Is Nothing Then
For Each cell In myRange
If cell.Value > 0 And cell - Date < 14 Then
cell.Interior.ColorIndex = 3
Else
cell.Interior.ColorIndex = -4142
End If
Next
End If
Application.EnableEvents = True
End Sub

 

[Jacob Skaria]

Oops. the formula should be .

=DATEDIF(TODAY(),A1,"d")<14

If this post helps click Yes