VB Code Help

Get_Involved

Active Member
Joined
Jan 26, 2007
Messages
383
In Cell D16 I have a Date. Is there a VB Code for this Idea?
If up till 90 days before the date the cell background remains white or no fill.
If from 90 days to 60 days, the cell background turns green.
If from 60 days to 30 days, the cell background turns yellow.
If 30 days or less the cell background turns red.
I have tried conditional formatting each cell, and can only get one cell to change to the color.
I know I don’t know exactly what I am doing with code
If its VB Code would you Please explain what page to put the code.
Any help would be appreciated.
Thanks
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

Logit

Well-known Member
Joined
Aug 31, 2016
Messages
4,594
What method did you use to change the single cell color ? Conditional Formatting ? What was the formula used ?

Were you targeting D16 with the Conditional Formatting ?

Did you have a date in D16 when the cell color changed ? What date were you using 90, 60, 30 days ?


I have tried conditional formatting each cell, and can only get one cell to change to the color.
Are there other cells you want to change color as well or just D16 ? Do the same rules apply to the other cells (90, 60, 30) ?
 
Upvote 0

Get_Involved

Active Member
Joined
Jan 26, 2007
Messages
383
Thanks Logit For Your Reply
Conditional Matted
The formula is =D16<=E19
In E19 I have A date that is 90 past the date in D16. Green it works
In E20 I have A date that is 60 past the date in D16. Green Supposed to be Yellow it does not work
In E20 I have A date that is 30 past the date in D16. Green Supposed to be Red it does not work
 
Upvote 0

Get_Involved

Active Member
Joined
Jan 26, 2007
Messages
383
Thanks Logit
I must not have understood the link you gave me.

In cell D16 I have a date, 03/17/17.

This date is an expiration date.

I have tried to condition format this cell to change to green when todays date is within 90 days, and yellow within 60 days, then red within 30 days.

No matter how I arrange them, the cell stays whatever color when it gets to the first format.

Is there a way to write this formula so that it only is green from 90 to 60 days, then turns yellow from 60 to 30 days, then turns red from 30 days?

D16 never changes till I set a new expiration date.

Maybe this explains what I am trying to do.
 
Upvote 0

Logit

Well-known Member
Joined
Aug 31, 2016
Messages
4,594
.
.
Code:
[TABLE="width: 64"]
 <colgroup><col width="64" style="width:48pt"> </colgroup><tbody>[TR]
  [TD="class: xl63, width: 64"]For 90  Days:[/TD]
 [/TR]
 [TR]
  [TD]=AND($D16-TODAY()>=60,  $D16-TODAY()<=90)[/TD]
 [/TR]
 [TR]
  [TD][/TD]
 [/TR]
 [TR]
  [TD="class: xl63"]For 60 Days:[/TD]
 [/TR]
 [TR]
  [TD]=AND($D16-TODAY()>=30,  $D16-TODAY()<=59)[/TD]
 [/TR]
 [TR]
  [TD][/TD]
 [/TR]
 [TR]
  [TD="class: xl63"]For 30 Days:[/TD]
 [/TR]
 [TR]
  [TD]=AND($D16-TODAY()>=60,  $D16-TODAY()<=90)[/TD]
[/TR]
</tbody>[/TABLE]

All three formulas go in the same Conditional Formatting for cell D16. Enter the first formula and save. Then add a new formula and save, etc. etc.
 
Upvote 0

Forum statistics

Threads
1,190,825
Messages
5,983,106
Members
439,824
Latest member
nellyc

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top