Excell Sum calculation [Closed]

Report
-
Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
-
Hello,
I'd like to find out how to make an excell sum to highlight when a point in time has passed.
For example. say I buy a tin of beans on 12/3/09, if i input that date, I want a sum that highlights that it goes out of date in six months and tells me that date.also i want it to turn red if has passed that date.

Can anyone help

1 reply

Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
797
I presume the data is 12th march. in that case in my version and regional configuration it should be entered as 3/12/09.
it is entered in A!

right click the sheet tab and click view code and in the resulting window copy paste this EVENT CODE. whenever you activate this sheet this event code is invoked. If 6 months(180 days) are over then B11 will have entry "expired" and A1 will be in red.

Private Sub Worksheet_Activate()
Dim x As Integer
Range("A1").Interior.ColorIndex = xlNone
Range("B1").Clear
x = Date - Range("A1")
If x > 180 Then
Range("B1") = "expired"
Range("A1").Interior.ColorIndex = 3
End If
End Sub

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!