Copying fill color

[Billmc0714]

How i get the fill color of a cell on page 2 when its linked to a cell on
page 1

 

[Gord Dibben]

If the source fill color is due to Conditional Formatting, replacate that CF
in the target cell.

Otherwise...........linked formulas, as all formulas, return values only,
not formatting.


Gord Dibben MS Excel MVP

 

[Billmc0714]

--
Bill McClelland

If the source fill color is due to Conditional Formatting, replacate that CF
in the target cell.

Otherwise...........linked formulas, as all formulas, return values only,
not formatting.


Gord Dibben MS Excel MVP

 

[Billmc0714]

I dont think i was very clear in my question so I will try again On page 1 I
have cell 1 a with a value and a cell color and I want to change both the
cell value and the cell cover I know if I use Conditional Formating the value
will change on page 2 but i can not get the cell color to change on page 2
without manually changing it the condtional formating does not change the
cell color on page when I change it on page 1

 

[Gord Dibben]

I thought your first post was clear.

You want to link a cell on sheet2 to a value in a colored cell on sheet1.

You want to also link the formatting of sheet1 cell.

Cannot be done without CF for value on both cells.

I don't know what you're trying for CF but if sheet1 CF is something like

Cell value = <123 use the same CF on sheet2


Gord

 

[Billmc0714]

Excel gives me an error message and i quote "You can not refernce another
work page or work shett when using Conditional Formating"

 

[Gord Dibben]

Why are you trying to reference sheet1 cell on sheet2 in CF?

The link is already there, just give sheet2 cell same CF as sheet1 cell.


Gord Dibben MS Excel MVP

 

[Billmc0714]

So The real answer is you can not make the cell fill color change on sheet
two if its linked to to sheet one only the values change

 

[Billmc0714]

I have tried all that you suggested but none of it works I just wanted a way
so that the cell fill color change on sheet 2 when I changed it on sheet 1

 

[Gord Dibben]

Start with A1 on sheet1

Enter a number like 3

Format>CF>Cell value is: between 1 and 10

Format to yellow.

Go to A1 on sheet2 and enter =sheet1!A1

Format>CF>Cell value is: between 1 and 10

Format to yellow.

Go to sheet1 and change A1 number to 12

Color goes away on both cells.

Change sheet1 A1 to 6 and color comes back to both cells.

There is no other way to change the fill color on sheet2 without the CF or
VBA.

Color change is not a trappable event so even with VBA it would be difficult
AFAIK and probably require Case statements based on colorindex number of
source cell.

As I said earlier..........you cannot link colors in any straightforward
manner.


Gord

 

[Billmc0714]

Thank you
--
Bill McClelland

Start with A1 on sheet1

Enter a number like 3

Format>CF>Cell value is: between 1 and 10

Format to yellow.

Go to A1 on sheet2 and enter =sheet1!A1

Format>CF>Cell value is: between 1 and 10

Format to yellow.

Go to sheet1 and change A1 number to 12

Color goes away on both cells.

Change sheet1 A1 to 6 and color comes back to both cells.

There is no other way to change the fill color on sheet2 without the CF or
VBA.

Color change is not a trappable event so even with VBA it would be difficult
AFAIK and probably require Case statements based on colorindex number of
source cell.

As I said earlier..........you cannot link colors in any straightforward
manner.


Gord

 

[Gord Dibben]

Easier method to creating a link and formatting.

After setting CF on sheet1 A1

Copy that cell and select A1 on sheet2

Paste special>paste link>OK>Esc

Paste Special>Formats>OK>Esc


Gord