Keeping cells colored

[Tonso]

I have an XL2003 spreadsheet in which i want cells a11:b15 to always
be red, and c16:c20 to always be blue. Problem is, i have to copy data
into these cells from cells that have no color. Sometimes the range i
copy from will have 1 row only, sometimes 2, sometimes 3, up to 5, so
sometimes i copy into a11:b11, sometimes a11:b12, etc. After i copy i
would like the cells copied into to return to red, or blue.

Thanks,

Tonso

 

[GS]

Tonso explained on 3/18/2011 :

I have an XL2003 spreadsheet in which i want cells a11:b15 to always
be red, and c16:c20 to always be blue. Problem is, i have to copy data
into these cells from cells that have no color. Sometimes the range i
copy from will have 1 row only, sometimes 2, sometimes 3, up to 5, so
sometimes i copy into a11:b11, sometimes a11:b12, etc. After i copy i
would like the cells copied into to return to red, or blue.

Thanks,

Tonso

Color the cells however you like. When pasting data use
PasteSpecial>Values.

 

[Gord Dibben]

After pasting click on the Paste Options button and "Match Destination
Formatting"

It is an extra step after the paste but is the only way unless you employ VBA
event code.


Gord Dibben MS Excel MVP

 

[CellShocked]

After pasting click on the Paste Options button and "Match Destination
Formatting"

It is an extra step after the paste but is the only way unless you employ VBA
event code.


Gord Dibben MS Excel MVP

I fill the cell via lookup to another cell further across in the row..
That way the cell(s) all stay exactly the way you format them, and the
data is all that varies, and you do that by changing the data in the
lookup target cells and leave the cells you want left alone... alone..

So, I would fill a table with data (a row) and name each cell up in the
upper left corner as a named range. Then, in the target cell, you place
the formula: =rangename where "rangename" is where you would place the
names you gave the lookup cells.

You may be able to simple state the cell location as "=A9" in your "A1"
cell to fill it with the A9 data but keep its own formatting.

The other way around it is to conditionally format the cell so that if
it contains certain data, it appears a certain way.

 

[Tonso]

Tonso explained on 3/18/2011 :


Color the cells however you like. When pasting data use
PasteSpecial>Values.

--
Garry

Free usenet access athttp://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

Thanks to all! I will use the Paste Value Option Garry. Gord, I am not
familiar with the "Match Destiantion Formatting" feature.

Thanks again!!

 

[GS]

Tonso formulated on Sunday :

Thanks to all! I will use the Paste Value Option Garry. Gord, I am not
familiar with the "Match Destiantion Formatting" feature.

Thanks again!!

The 'Match Destination' feature is available from the context menu that
appears after pasting. (a square with a + sign and a dropdown arrow)

 

[Gord Dibben]

Paste Value is not a good plan if any of your copy/paste operations are
comprised of formulas.

The Show Paste Options button has to be enabled under Excel
Options>Advanced>Cut, copy and paste section.


Gord

 

[CellShocked]

Tonso formulated on Sunday :

The 'Match Destination' feature is available from the context menu that
appears after pasting. (a square with a + sign and a dropdown arrow)

I have a spreadsheet that has a number of cells filled from elsewhere
that I selectively highlight individual by having two columns of "Y" "N"
cells that I give range names to. The highlight by formula code is:
=IF(Show1_1="Y",Name1,"") The variables are the range names of "Show1_1",
which increments, and "Name1" which increments.

Since the data evaluation will always be true, the formula is used to
evaluate the "Y" "N" value of that range name cell (ShowX_X) and
highlight only if that value is set to "Y".

It works fine, but I have data in one cell that has a dash in the data
string, and I cannot get that cell to follow suit.

Do I use "INDIRECT" to call that range name or what? I cannot figure
out why the cells that contain alpha numeric data will not obey the same
conditional formatting rules as the rest of the cells I apply it to.


What this allows me to do is fill a table of data from another location
and that way I control the way the "table" looks when printed, etc.
IOW, I can selectively highlight selected cells by using my "Y" "N"
method.

The highlighted cells are controlled by a set of "Y" "N" cells off to
the right of the "report form".

 

[Mark Datter]

Well, is there anyone out there familiar with this?