Compare cell colour to other cell colour and trigger action automatically

[Maxime_Excel]

Hi,

I have a worksheet with a few columns and rows.

INPUT TABLE

Group name	Wk 1	Wk 2	Wk 3	Wk 4
Group 1	red	red
Group 2	yellow	yellow
Group 3	red	purple

Every week we review the performance of the different groups and colour the inside of the cell in certain colour, manually: green, orange, red, purple, ... (they all have their own meaning). In the table above, I wrote "red" but the cell is actually coloured in red, AND there is a number inside the cell, so I cannot write "red" inside either.

OVERVIEW TABLE

In another table, we want to highlight in which week a significant change occured. A significant change = a colour change. So if this week is Wk2 then group 3 has a colour change and we want to show that with the colour black.
The Overview table would look like:

Group name	Wk 1	Wk 2	Wk 3	Wk 4
Group 1	white	white
Group 2	white	white
Group 3	white	black

I know about the GET.CELL formula. It would be easy to use this formula, however the GET.CELL formula doesn't trigger an event based on a colour change. For an event to happen, I need to enter the formula again - which is not what we are aiming for.

Is there a different way to compare a cell colour with another cell colour and launch a formula based on a IF, THEN, ELSE? Keep in mind that the cell colours will change, it's not a one time event (then GET.CELL would have been perfect).

Thanks!

 

[Fluff]

There is no way a formula can see the colour/format of another cell. Let alone trigger if the cell colour changes.
Even VBA cannot detect the change of cell colour.

 

[Maxime_Excel]

There is no way a formula can see the colour/format of another cell. Let alone trigger if the cell colour changes.
Even VBA cannot detect the change of cell colour.

I was not ware of that. I guess I will always have to click "recalculate", which is fine. GET.CELL doesn't react to "recalculate" though, so that is still not an option.

Regarding the actual formula: how can I compare the colour of one cell with the colour of another?

 

[Fluff]

You cant. As I said a formula cannot see the colour/format of other cells.

 

[Maxime_Excel]

You cant. As I said a formula cannot see the colour/format of other cells.

I understood that VBA cannot trigger an action based on a colour change, but I thought VBA can read the colour of a cell?

 

[hagia_sofia]

I understood that VBA cannot trigger an action based on a colour change, but I thought VBA can read the colour of a cell?

You can compare cell colors based on results of GET.CELL as color will be represented by a number. As for recalculation you can help it with something +NOW()-NOW().

 

[Fluff]

I thought VBA can read the colour of a cell?

It can, but you were talking a bout a formula.

 

[Maxime_Excel]

You can compare cell colors based on results of GET.CELL as color will be represented by a number. As for recalculation you can help it with something +NOW()-NOW().

Do you mind explaining what you mean?

I know how to get the number from the colours, I have them visible, but how can I automatically re-calculate the comparison between old number and new number when a colour gets updated?
Let's say the following:

1	A	B	C
2	Group name	Wk 1	Wk 2	Wk 3	Wk 4
3	Group 1	3	3
4	Group 2	6	6
5	Group 3	3	29

Overview table

1	A	B	C
2	Group name	Wk 1	Wk 2
3	Group 1	white	WHAT SHOULD THIS FORMULA BE?
4	Group 2	white	WHAT SHOULD THIS FORMULA BE?
5	Group 3	white	WHAT SHOULD THIS FORMULA BE?

Imagine that we just added the colours of Wk 2 in the first table. How will the Overview table update automatically?