Need to link formatting of cells within the same worksheet

Hello there

I have a situation where I need to link two cells on the same worksheet – but with a difference.
My requirement dictates that when the source cell changes, the linked cell needs to mimic the source cell exactly. This includes formatting of the source cell and in particular the background color. Using a formula such as =A1 for example will return only the value of the cell. I need to copy/ mimic the background color of the source cell as well. Would anyone have any ideas how this function could be achieved?

Thanks and best regards,

Fartnuckles

Originally Posted by Fartnuckles

Hello there

I have a situation where I need to link two cells on the same worksheet – but with a difference.
My requirement dictates that when the source cell changes, the linked cell needs to mimic the source cell exactly. This includes formatting of the source cell and in particular the background color. Using a formula such as =A1 for example will return only the value of the cell. I need to copy/ mimic the background color of the source cell as well. Would anyone have any ideas how this function could be achieved?

Thanks and best regards,

Fartnuckles

Hi Fartnuckles,

Can you tell me how the background colour of A1 changes colour, has it got conditional formatting linked to it, if so set up your other cell to the same formatting.

oldchippy

Hello Oldchippy,

Ok - The data in each of the source cells represents an amount that has been say – quoted. The background color of the cells are manually colored by our sales reps depending on weather a quotation has been accepted in full (Green) partially accepted (Orange) and not accepted (RED). Our Sales reps find it easier just to fill the background color of a cell to indicate at a glance the current status of a quote. Management have their own copy of the spreadsheet and I simply link the relevant cells (copy, paste special , paste link) between the sales sheet to the management sheet to import all of the data required. This technique normally works well but unfortunately the cell background color will not link. I don’t think conditional formatting will work in this scenario. I get the feeling that Excel is very limited in it’s handing of colors. Any help in importing cell formatting would be appreciated.

Thanks again

Fartnuckles.

Originally Posted by Fartnuckles

Hello Oldchippy,

Ok - The data in each of the source cells represents an amount that has been say – quoted. The background color of the cells are manually colored by our sales reps depending on weather a quotation has been accepted in full (Green) partially accepted (Orange) and not accepted (RED). Our Sales reps find it easier just to fill the background color of a cell to indicate at a glance the current status of a quote. Management have their own copy of the spreadsheet and I simply link the relevant cells (copy, paste special , paste link) between the sales sheet to the management sheet to import all of the data required. This technique normally works well but unfortunately the cell background color will not link. I don’t think conditional formatting will work in this scenario. I get the feeling that Excel is very limited in it’s handing of colors. Any help in importing cell formatting would be appreciated.

Thanks again

Fartnuckles.

Hi Fartnuckles,

Here's a way round this, see the screen shot attached. If you go and find the camera and drag it up to one of the toolbars so that it is always available, then place the curser on B3, click the camera, then place the "picture" over D3, size accordingly. Now when the rep changes the background colour or the cell contents, this is immediately updated in the "picture".

Hope this helps, let me know

oldchippy

Top stuff Oldchippy - now that's smart thinking! It works like a charm.

Thanks for the feedback, glad to be of help