Format Cell Color Permanently

[Cremorneguy]

Hi there,

Is there a way to permanently format a column of cells with a color?

I want an entire column to be the color green but when i move the values from this column, i want the color to stay in the column and not move with the values i'm moving, when i move the values to a new column, i want the values to adapt to the color of the cells in the new column (i.e. this column is yellow).

Is this possible?

Thanks

[rajansettu]

hi use fill color for a cell or column

[newdoverman]

Is this what you are after:

1./ Select the data and copy.

2./ Go to the destination and PASTE VALUES.

3./ Select the original data and on the Home tab, Editing Group, Clear, Contents.

Just moving a range will move the formatting as well as the contents together.

[Cremorneguy]

Hi Newdoverman,

No thats not what i want to do. I want the cells to always stay the same colour no matter how many times i move the data, i want the column to always stay the same colour (i.e. Green) so that if i move data to another cell, only the values move, not the cell colour. At the moment if i move data, the cell value and the colour moves leaving the area with a white background which is not what i want.
I have other people that will use the spreadsheet also who aren't skilled at all with excel, so telling them what you replied would be pointless.

Thanks for the reply though

[newdoverman]

Then the short answer to your question is NO unless you use VBA to move the contents.

[shg]

You could use conditional formatting based on column.

[martindwilson]

how are you"moving" the data

[Cremorneguy]

just by selecting it and dragging it across.

[martindwilson]

as shg says
you can create a rule of for column a of =column()=1 pick a colour then
repeat for column b =column()=2 may get a bit heavy on formats if you do a whole workbook

[newdoverman]

I understand that the OP wants the formatting of the original position of a value to remain while the value is dragged to another location.

In my experiments, no matter how I use conditional formatting to format columns with a colour, dragging value from one column to another removes the conditional formatting for the cells concerned in both columns.

I used =COLUMN()=1 =COLUMN()=2 etc

and this one that drags the formatting with the value leaving the original cell with no background.

=mod(column(),1)=0 =mod(column(),2)=0 etc

If I'm doing something wrong, I'd sure like to know.

[shg]

It works fine for me using font color instead of fill. I find Excel 2010 CF for fill very erratic.

[newdoverman]

The font colour seems to "stick" while the background is to say the least "flaky". I just did some more testing and the "Applies To" really gets messed up after making a couple of moves of data.

I am finding that if you combine the font colour with a background colour the background colour formatting takes over and the font colour no longer "sticks".....very frustrating feature to work with if the editing isn't complete and not so friendly under normal circumstances.

Fighting with Conditional Formatting leads to hair styles like mine