I have a group of cells in a column that I would like to "format" individualy/independantly of one another and worksheet date (seperate cell to drive other functions) to change colors (the entire cell) red, yellow, green based on the number of days elapsed from the date in the cell NOT the date on the worksheet. For example cells D52 through D61 all contain different dates. I would like the cell to fill (based on their dates) green if it is greater than ten days from the date in the cell, than yellow if it is the tenth day from the date in the cell and red if it is greater than 10 days from the date in the cell. Is this possible? I 've been scrubbing numerous sites and such and "googling"- no luck. If so, It just wasn't dumbed down enough for me to comprehend. Any help would be appreciated. Thanks a lot.
Excel Excel 07 conditional format? (Date functions)
- Thread starter wads
- Start date
- Joined
- Sep 3, 2008
- Messages
- 164
- Reaction score
- 5
Hi wads,
I have a case statement that may help you along. This changes the format of cells within a defined range. myplage2 is the range, in the sample it is named. You can change it to the rc:rc format if you need.
The event should be loaded in the worksheet change. Then every time you submit a change to the worksheet it will update the formatting.
Here is a code snip:
Set myplage2 = Range("bupercent")
For Each cell In myplage2
Select Case cell.Value
Case 95 To 100
cell.Interior.ColorIndex = 4
Case 85 To 94.99
cell.Interior.ColorIndex = 6
Case 75 To 84.99
cell.Interior.ColorIndex = 46
Case 0 To 74.99
cell.Interior.ColorIndex = 3
Case Else
cell.Interior.ColorIndex = xlNone
Stoneboysteve
I have a case statement that may help you along. This changes the format of cells within a defined range. myplage2 is the range, in the sample it is named. You can change it to the rc:rc format if you need.
The event should be loaded in the worksheet change. Then every time you submit a change to the worksheet it will update the formatting.
Here is a code snip:
Set myplage2 = Range("bupercent")
For Each cell In myplage2
Select Case cell.Value
Case 95 To 100
cell.Interior.ColorIndex = 4
Case 85 To 94.99
cell.Interior.ColorIndex = 6
Case 75 To 84.99
cell.Interior.ColorIndex = 46
Case 0 To 74.99
cell.Interior.ColorIndex = 3
Case Else
cell.Interior.ColorIndex = xlNone
Stoneboysteve
Hmmmm. Could you pretend I just created my first spread sheet yesterday and break it down for me a little more perhaps? That is not the case (I've used excel a few times more often than not I tweak/cut/copy existing spreadsheets formulas and such) however I am not a hard core excel user. Not really called for in my job (aircraft electrician). I just want to simplify the archaic tracking systems we have in place. I appreciate the help. Thanks.
-wads
-wads
- Joined
- Sep 3, 2008
- Messages
- 164
- Reaction score
- 5
Hi wads,
I will make you a deal, you post the spreadsheet as an attachment to the forum with your requirements and if I can I will provide you the solution. I will post the solution, or my attempt, to the forum. After all, we are all here to learn and to pass knowledge.
To answer your post, the code I posted goes into the worksheet change event in the VB editor. It makes the format changes each time you make a worksheet change. The VB editor can be accessed by tools>macros>visual basic editor. Above the script editing pane you can select the worksheet change event.
Stoneboysteve
I will make you a deal, you post the spreadsheet as an attachment to the forum with your requirements and if I can I will provide you the solution. I will post the solution, or my attempt, to the forum. After all, we are all here to learn and to pass knowledge.
To answer your post, the code I posted goes into the worksheet change event in the VB editor. It makes the format changes each time you make a worksheet change. The VB editor can be accessed by tools>macros>visual basic editor. Above the script editing pane you can select the worksheet change event.
Stoneboysteve