Set background in Cell depending on another cell's background

Hello,

In a range of cells (A1..d8) which have background set with multiple colors. Is it possible to set another range of cells (H1..K8) whith the same backgrounds as the other range and so that if one cells background changes the cell in the second range whill also change to that new color?

kind regards,

robbert

You can use the following line to copy the colors from one range to the other.

Please Login or Register  to view this content.

Now depends only on how or when you want the procedure to start. (eg. with a button, on events, on calculations, etc.)

Hope it helps.

Thanks for the reply.

I would like the cells to be updated whenever something changes on the worksheet, is this possible?

kind regards

robbert

Sure it is;
Just use the "Worksheet_Change" feature inside the worksheet you want to trigger the macro:

Please Login or Register  to view this content.

unfortunately when I run the code the fields H1..K8 stay white (no background) any ideas?

robbert

It should work.
If you send me the file I might take a quick look and maybe help you further.

here the file attached.

kind regards,

robbert

Wizz,

my mistake it works.
My only problem is that the range it will copy from and the range it will copy to could grow or shrink for example the range could change to a1..d4 and so also the target range. Is this also possible to be solved?

Yes it is possible.
Just need to know:
a) Is the offset amount always the same or it will always be different.
b) The position were you start asigning the new colors is always the same or it is also different.

a) the offset amount will increase
B) also the position where we start assignin the new colors will be different

The following procedure should help.
To see how it works:
1.Select the desired range you want to copy its background colors
2.Click over the grey circle.
3.A msgbox will popUp. Write inside the address of the cell were you want to start painting.

Please Login or Register  to view this content.

Now is up to you to modify it depending on your needs.

is it possible to use named ranges in this code? The named ranges will automaticly change when coloms are added.