Conditional Formatting between dates

[jeckjeck_13]

Hi to all,

I want to ask for help to set up an excel sheet with a formula. The scenario is like this:

We need to track the batteries if it is expired or not.Expiration is 3 months. once you received the battery, you need to put the date in the sheet (A1) and then the expiration will be automatically calculated in B1. another also, in B1, will change its color according to how may days left before the expiration date.

black color if the expiration is < 15 days
red color if the expiration is <30 days
yellow color if the expiration is between 31 and 44 days
green color if the expiration is > 45 days
white color if B1 is blank

If the expiration is <30 days, there should be an alert. it either a message will pop up or anything that will make a very good alarm.

Please help me to make this sheet.

Regards,
ernest

 

[jeckjeck_13]

Sir Peter,

Thanks for this codes... I will try to do it and I will give you an update about what happened. in the mean time, can you send me a final product of what you have done? can you attach it here? Thanks a lot Sir Peter.

Regards,
Ernest

 

[jeckjeck_13]

Sir,

I have tried to manage to follow your instructions but unfortunately it doesn't work with me. Maybe I have mistakes but I followed exactly what you have written in here. The formulas are working fine but the cell highlighting effect is not working anymore and the message pop-up doesn't work while opening and and closing the sheet.

Regards,
Ernest

 

[cummingsea]

Peter SSs I have tried your code in Excel 2007 whin I open or close the file I get a pop up dialog box that reads: Good 2, Near 1, Need to replace 1, Bad 1, when I ckick on OK it then goes to sheet 2, again I click on ok and it goes to sheet 3, then I click on OK and it goes away untill I close the file, also highlights in column B Expire Dates, so I would say it works. Ops just noticed that in column B (expire date) the 3rd entry (7/9/2008) did not change color and the 5th entry (6/4/2008) did not change color.

 

[jeckjeck_13]

Sir Peter,

I tried it also in excell 2007 and it works fine... maybe I have done something wrong before. I try to test the sheet andI found one problem. whenever I am going to delete an existing value in receiving date (column A), there is a bug with the VB code. try to put one date on column A for example 09-jul-2008. time will come that I need to replace this date. if you try to delete 09-jul-2008, error will pop-up.... please help to fix this.... And also when the battery is expired, the color black will disappear. can we make the row A1 to D1 black with white font if it is expired? sorry if i am asking for a lot of things.. thanks a lot for helping me about this.

Regards,
Ernest

 

[Peter_SSs]

Sir Peter,

I tried it also in excell 2007 and it works fine... maybe I have done something wrong before. I try to test the sheet andI found one problem. whenever I am going to delete an existing value in receiving date (column A), there is a bug with the VB code. try to put one date on column A for example 09-jul-2008. time will come that I need to replace this date. if you try to delete 09-jul-2008, error will pop-up.... please help to fix this....

Yep, I thought I had allowed for that but I hadn't. Shouldn't be too much trouble to fix that. Before I do, however, I would like clarification about...

can we make the row A1 to D1 black with white font if it is expired?

When the battery has expired (that is column B date is equal to or before the current date) you want columns A:D to be white text on black background. For all the other circumstances, is it just column B to colour like my previous code or are they all supposed to colour columns A:D?

Also, when < 15 days you also want black (which column(s)?). So we have black for < 15 days and black for expired items. Is that correct?

 

[jeckjeck_13]

oh I'm sorry Sir, what iI mean is A2:D2.. As of now this is our status:
<15 days will be black w/ white font on column B

then if it is expired there will be no color right?...

what I suggest is that whenever it expires, the whole row for that battery will be all black with white font. it means that this battery can no longer be use and it is a good indication that the user will never forget to change that.

I have one more question Sir Peter, If I will ad a column on the left, it will still work or not? do we have a solution for that? for example i want to put a serial number on the leftmost side. it means the receive date which is in column A will move to column B. Is it possible?

Summary:
<15 days - column B will be Black w/ White font
<=0(expired) - rows on that particular date will be Black background w/ white font

If possible, Adding columns on the left will not disturb any formula or any VB codes. so we can add battery serial numbers on it.

***I like the pop-up message.. you're really the best in VB and excel.***


Regards,
Ernest

 

[Peter_SSs]

Ok, adding a column does affect the code. Basically we have to change all references to column "A" over to column "B". I have done that (I hope I got them all).

Replacement codes for those sections changed:

<font face=Courier New><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Workbook_BeforeClose(Cancel <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Boolean</SPAN>)<br>    <SPAN style="color:#00007F">Dim</SPAN> ws <SPAN style="color:#00007F">As</SPAN> Worksheet<br>    <br>    <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> ws <SPAN style="color:#00007F">In</SPAN> Worksheets<br>        CheckExpiry ws.Range("B2", ws.Range("B" & Rows.Count).End(xlUp)), <SPAN style="color:#00007F">True</SPAN>, ws.Name<br>    <SPAN style="color:#00007F">Next</SPAN> ws<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br><br><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Workbook_Open()<br>    <SPAN style="color:#00007F">Dim</SPAN> ws <SPAN style="color:#00007F">As</SPAN> Worksheet<br>    <br>    <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> ws <SPAN style="color:#00007F">In</SPAN> Worksheets<br>        CheckExpiry ws.Range("B2", ws.Range("B" & Rows.Count).End(xlUp)), <SPAN style="color:#00007F">True</SPAN>, ws.Name<br>    <SPAN style="color:#00007F">Next</SPAN> ws<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>



<font face=Courier New><SPAN style="color:#00007F">Public</SPAN> <SPAN style="color:#00007F">Sub</SPAN> CheckExpiry(<SPAN style="color:#00007F">ByRef</SPAN> myTarget <SPAN style="color:#00007F">As</SPAN> Range, <SPAN style="color:#00007F">ByVal</SPAN> OpenClose <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Boolean</SPAN>, <SPAN style="color:#00007F">ByVal</SPAN> wsName <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>)<br>    <SPAN style="color:#00007F">Dim</SPAN> Tday <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Date</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> chRng <SPAN style="color:#00007F">As</SPAN> Range<br>    <SPAN style="color:#00007F">Dim</SPAN> c <SPAN style="color:#00007F">As</SPAN> Range<br>    <SPAN style="color:#00007F">Dim</SPAN> iColor <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> fColor <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> Report(2, 5) <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> ReportString <SPAN style="color:#00007F">As</SPAN> String<br>    <SPAN style="color:#00007F">Dim</SPAN> i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> Expired <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Boolean</SPAN><br>    <br>    <SPAN style="color:#00007F">Set</SPAN> chRng = Intersect(myTarget, myTarget.Parent.Columns("B"))<br>    <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> chRng <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>        Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN><br>        Tday = <SPAN style="color:#00007F">Date</SPAN><br>        <SPAN style="color:#00007F">For</SPAN> i = 1 <SPAN style="color:#00007F">To</SPAN> 5<br>            Report(2, i) = 0<br>        <SPAN style="color:#00007F">Next</SPAN> i<br>        <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> c <SPAN style="color:#00007F">In</SPAN> chRng.Offset(, 1)<br>            fColor = xlAutomatic<br>            <SPAN style="color:#00007F">If</SPAN> IsDate(c) <SPAN style="color:#00007F">Then</SPAN><br>                <SPAN style="color:#00007F">If</SPAN> c.Value - Tday <= 0 <SPAN style="color:#00007F">Then</SPAN><br>                    <SPAN style="color:#00007F">With</SPAN> c.Offset(, -2).Resize(, 5)<br>                        iColor = 1<br>                        fColor = 2<br>                        Report(2, 5) = Val(Report(2, 5)) + 1<br>                        .Interior.ColorIndex = iColor<br>                        .Font.ColorIndex = fColor<br>                    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>                <SPAN style="color:#00007F">Else</SPAN><br>                    <SPAN style="color:#00007F">Select</SPAN> <SPAN style="color:#00007F">Case</SPAN> c.Value - Tday<br>                        <SPAN style="color:#00007F">Case</SPAN> <SPAN style="color:#00007F">Is</SPAN> < 15<br>                            iColor = 1<br>                            fColor = 2<br>                            Report(2, 4) = Val(Report(2, 4)) + 1<br>                        <SPAN style="color:#00007F">Case</SPAN> <SPAN style="color:#00007F">Is</SPAN> < 30<br>                            iColor = 3<br>                            Report(2, 3) = Val(Report(2, 3)) + 1<br>                        <SPAN style="color:#00007F">Case</SPAN> <SPAN style="color:#00007F">Is</SPAN> <= 45<br>                            iColor = 6<br>                            Report(2, 2) = Val(Report(2, 2)) + 1<br>                        <SPAN style="color:#00007F">Case</SPAN> <SPAN style="color:#00007F">Is</SPAN> > 45<br>                            iColor = 4<br>                            Report(2, 1) = Val(Report(2, 1)) + 1<br>                        <SPAN style="color:#00007F">Case</SPAN> <SPAN style="color:#00007F">Else</SPAN><br>                            iColor = xlNone<br>                    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Select</SPAN><br>                <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>            <SPAN style="color:#00007F">Else</SPAN><br>                iColor = xlNone<br>            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>            c.Interior.ColorIndex = iColor<br>            c.Font.ColorIndex = fColor<br>        <SPAN style="color:#00007F">Next</SPAN> c<br>        <SPAN style="color:#00007F">If</SPAN> OpenClose <SPAN style="color:#00007F">Then</SPAN><br>            Report(1, 1) = "Good = ": Report(1, 2) = "Near = "<br>            Report(1, 3) = "Need to Replace = ": Report(1, 4) = "Bad = "<br>            Report(1, 5) = "Expired = "<br>            ReportString = "For Sheet " & wsName & vbLf & vbLf<br>            <SPAN style="color:#00007F">For</SPAN> i = 1 <SPAN style="color:#00007F">To</SPAN> 5<br>                ReportString = ReportString & Report(1, i) & Report(2, i) & vbLf<br>            <SPAN style="color:#00007F">Next</SPAN> i<br>            Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN><br>            MsgBox ReportString<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>        Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

 

[jeckjeck_13]

Hi Sir,

It works! the entire row became black when the battery is expired. The only issue is whenever the entire row changes to black, and you try to change the date with not expired date, it remains black.... only the column C changes... it should go back to "no fill" background.

The bug is now fixed whenever you delete one date...

Now I can put the serial number... pop-up message is there....


Regards,
Ernest

 

[Peter_SSs]

The only issue is whenever the entire row changes to black, and you try to change the date with not expired date, it remains black.... only the column C changes... it should go back to "no fill" background.

OK, I've done a fair bit of the work here, what about you have a fiddle around with the code and see if you can progress towards fixing this problem?

One method would be to remove all colour from all columns (A:E) in the "changed" range and put all text to black, then let the code put back relevant colours in that range.

 

[jeckjeck_13]

Sir Peter,

I don't have enough knowledge in VB but in excel I have. Since I post this question here, I didn't stopped working on this sheet. I have tried to fix the issues I have found but I cannot fix it because the VB codes are advance. I will try my best Sir If I can manipulate the codes. Thanks a lot for the help and I hope I will be like you in the future.

Sir, Where is the part that I need to change and what code do I need to use?

Regards,
Ernest