Question about automated colorchanging

[RoelPeutie]

Question concerning automated colorchanging
Hi!

I've been working a bit on a new Excelsheet to get an overall view of the situation of my vehicles.

On my X-axis(row 1) I have the dates of januari 2012, entered using Format Cells / Date / (14-Mar).
On my Y-axis(column A) I have the names/numbers of my vehicles in text format.

Depending on the situation of the vehicle on a current date we use a certain color.

E.g.

Our Mercedes is gone for maintenance on the 2nd of February. The cell gets a red color.
The next day it's free. the cell gets no color.
The 4th it's gone on a mission. The cell gets a Blue color.

To get a clear view of the disponibility of my vehicles we manually color the name of the vehicles (in the A column) in the same color as the current date/activity combination.


Example 1.JPG

In this example we're on the 2nd of February.

On the 3rd I have to change the color of the vehicle's name (in the A column) back to white. The 4th to blue. And so on, and so on for the rest of the year.

I hope you're still following

Doing this manually is quickly forgotten or sometimes takes to much effort for some.

Is it possible to automatically change the fillcolor of the cells in the first column to the color of the cells under the current date? Using functions or scripting

E.g. that when we get to the 4th of February my color in the cell A3 ("Mercedes") changes automatically to blue.

Thx for your attention

[wamp]

Try this:

Please Login or Register  to view this content.

[RoelPeutie]

Hi Wamp,

Thx for a quick answer.
But ... it's not working. I see you store cCol but don't use it later. is there a reason?
I guess I have to change the ranges too?

Thx

[wamp]

Sorry, I posted a faulty code.
the correct would be:

Please Login or Register  to view this content.

also see attached example.

carMacro.xls

(then you could run that macro on 'workbook open' for example)