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

 

[Peter_SSs]

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

OK, for this bit, immediately before the line:
For Each c In chRng.Offset(, 1)

try adding these four lines of code:

<font face=Courier New><SPAN style="color:#00007F">With</SPAN> chRng.Offset(, -1).Resize(, 5)<br>    .Interior.ColorIndex = xlNone<br>    .Font.ColorIndex = xlAutomatic<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN></FONT>

 

[Peter_SSs]

Received via PM:

Sir I have followed your instruction and it works fine. What I am working right now is to change the pop-up message. I am trying to understand it so I can change it. What I want to do is to poop up a message where it will tell you the serial number which is near to expire. It means when the epiration date is <30 it will get the serial number and post it on the message. I hope I can do that.

Sir, Is this the code that makes the pop-up message?

If OpenClose Then
Report(1, 1) = "Good = ": Report(1, 2) = "Near = "
Report(1, 3) = "Need to Replace = ": Report(1, 4) = "Bad = "
Report(1, 5) = "Expired = "
ReportString = "For Sheet " & wsName & vbLf & vbLf
For i = 1 To 5
ReportString = ReportString & Report(1, i) & Report(2, i) & vbLf
Next i

What do you mean by Report(1,1) = "Good = "
What is vblf

Regards,
Ernest

1. "Is this the code that makes the pop-up message?" Yes.

2. "What do you mean by Report(1,1) = "Good = "
What is vblf"

Near the start of the CheckExpiry sub is "Dim Report(2, 5) As String". This establishes an array of 2 columns and 5 rows of string values. Report(1,1) = "Good = " puts the string "Good = " into row 1 column 1 of this array.

After processing all the "Case" statements in my code and the blue part of the code above there will be an array that effectively looks something like the blue section below. The array is stored in memory, I have just used a sheet to demonstrate the concept.

The red section of code then puts the sheet name and then cycles along the 5 columns of the array, adding both row 1 (the words) and row 2 (the numbers of each category) into a long string.

vbLF (vb LineFeed) starts a new line in that string each time it occurs. That is why each category is shown on a new line in the message box.

You will somehow have to collect the serial numbers of all the batteries where the expiration date is < 30. That would happen for "Case Is < 15" and "Case Is < 30" in my previous code.

Excel Workbook
	A	B	C	D	E	F	G	H
2			Column
3			1	2	3	4	5
4	Row	1	Good =	Near =	Need to replace =	Bad =	Expired =
5		2	3	2	0	4	1
6
Array Structure

 

[jeckjeck_13]

Sir,

If I need to pop-up a message for which serial numbers that I need to replace, I will change reporting part of the code by this:

Dim Report(1,2) As String

If OpenClose Then

If c.value - Tday <30 Then

Report(1,1) = "Need to Replace Serial Number = "

ReportString = "For Sheet " & wsName & vbLf & vbLf
For i = 1 to 2

ReportString = ReportString & Report(1,i) & Report(2,i) & vbLf
Next i
Application.ScreenUpdating = True
MsgBox ReportString

End If

End If

Is this correct?

Regards,
Ernest

 

[Peter_SSs]

Is this correct?

Did you try it? Did it work?

If not, in what way did it not work?
- Error message
- Wrong results?
- Nothing happened?
- What changes did you make to the "Select Case" section of code to try to capture the relevant serial
number(s)

 

[jeckjeck_13]

Hi Sir,

How can I get the serial number if the expiration date is <30 days? which command do I need to use? The format of the serial number will be 3 digit numbers. for example 001, 050, 129, 159, etc.... I was able to change the message box but my problem is how can I get the value of one cell if the condition is met.

Regards,
Ernest

 

[chatzmc81]

i have a similar question but have not had any help peter could you help me please

 

[Peter_SSs]

i have a similar question but have not had any help peter could you help me please

Just post your question, if you haven't already. I try to look at as many threads as I can, and if I come across yours and think I can help, I will.