# Change a value and color based on time of implementation

#### lkalis

##### New Member
I'm in need of some assistance. I work for a school district, and have been asked to help with computer inventory. I have listed the inventory in an excel spreadsheet (excel 2003). Based on the make, model, and speed of the machines, I have assigned them a letter grade of A, B, C, D, and F. I did this to make it easier for each dept. to better understand when they need to start replacing their machines. I would like the letter grades of A, B, C, & D to automically progress to the next letter after two years of them being added to the excel sheet. Once it reaches the F status, it may remain there until it is replaced. To better help each dept. run down the list, I would also like each letter grade to automatically change to a unique color. (i.e. A = green, B = Blue, C = Orange, D = Black < Bold >, and F = Red < Bold >).

Can something like this be done?

Lou Kalis

### Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

#### RalphA

##### Well-known Member
It all sounds very doable.

The first part can be done by formulas (in which I could help you) or by VBA code.

The second part can be done by VBA code.

So, it seems to me that VBA code would be the best way to solve the problem. And, since I do not work with VBA code, it will have to be someone else that helps you.

#### lenze

##### Legend
Welcome to the board!!

Yes, this can be done, but might I suggest you change your rating system to numbers(1,2,3,4,5). It would simplify things.
Also, is there a date column? This would also make things easier. If these changes are not possible, I believe we can still get you what you want. I, as will others. will think on this.

lenze

#### lkalis

##### New Member
The rating system certainly can be changed.

I chose the A, B, C, D, F rating to help identify the machines with certain dept. heads that are teachers. It seems the more simple I make things for them, the better they respond. However, the color scheme will also help them identify what condition the machines are in. I believe that will be sufficient.

Thank you both for responding so quickly. It's nice to see other use their time to help others. I do appreciate your assistance.

Where shall be proceed from here?

Thank you,

Lou Kalis

#### joelnichols

##### Active Member

Something like this might be close to what you want. The color would have to be a VBA solution, and I am terrible at code, so someone else can pick that up i'm sure.
Book6
ABCDE
212/14/2005Rank
4
5IBM1.28/20/20014C
6Dell1.41/26/20023B
7Compaq29/15/20041A
8Sony2.22/18/20050A
9Dell2.43/5/20050A
10Ibm1.65/12/20032B
11Compaq19/12/19996D
12AtariTurtle7/5/198619F
Sheet1

#### lkalis

##### New Member
Question on the Years Since Added formula

I like your approach to this. Thank you. When I attempt to use the formula in the Years Since Added column (D5,D6, D7), I get a different response that you have. When I enter it exactly at you have, I end up with the following calculations in the first 3 Cells: 1/4/00, 1/3/04, 1/1/00. I have tried it on a PC running Office 2003, and a Mac running Office 2004. ( I did change the Mac to use the 1900 date system, rather than the 1904). It appears that the years are showing up in the month field. I do like how you have it only showing the years since added, rather than days/months. What step do you think I need to add? Thank you so much for your assistance.

Lou Kalis

#### joelnichols

##### Active Member
Cells D5:D12 must be formated as general or number instead of date. I should have mentioned that.

#### lkalis

##### New Member
Thank you !

Joel,

Thank you for the solution. That did the trick. I see what I can figure out on the color, as it helps sort machines, when they get into the hundreds.

You've helped tremendously. Thank you to everyone else who read and contributed to the task.

Sincerely,

Lou Kalis

Replies
10
Views
112
Replies
1
Views
107
Replies
1
Views
123
Replies
5
Views
191
Replies
7
Views
192