Conditional Formatting Copy Past?

C

Christine

Hi,

I have a tab in a worksheet with a column that contains conditional
formatting. When I copy and past it from one work sheet to another I lose
the conditional formatting. Is there anyway to keep this?

Thanks, Rob
 
D

David McRitchie

Hi Christine,
Conditional Formatting like other formatting is copied, when you
copy and paste, even to another worksheet.

Are you pasting only values with Paste Special.
Are you expecting some other cells to change as well.

What formula are you using in your Condition Formatting.
When you paste something without formatting into a cell
with formatting, you will lost the formatting.
 
C

Christine

Hi the conditional formatting changes the color of the cell, ie 1 = red. 2 =
yellow, 3 = green.

When I past I just get the numbers not the colors..

Thanks, C

David McRitchie said:
Hi Christine,
Conditional Formatting like other formatting is copied, when you
copy and paste, even to another worksheet.

Are you pasting only values with Paste Special.
Are you expecting some other cells to change as well.

What formula are you using in your Condition Formatting.
When you paste something without formatting into a cell
with formatting, you will lost the formatting.

---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

Christine said:
Hi,

I have a tab in a worksheet with a column that contains conditional
formatting. When I copy and past it from one work sheet to another I
lose
the conditional formatting. Is there anyway to keep this?

Thanks, Rob
Click to expand...
Click to expand...
 
E

Earl Kiosterud

Rob, (Christine?),

That shouldn't be happening. Give us the specifics on the conditional
formatting in a cell you're copy/pasting. If it's a "formula is" CF, give
us the formula, and tell us which cell it's for too. If you're pasting into
a cell that has the conditional formatting, it will overlay it with all
formatting from the copied cell (wiping out existing CF in the target cell).
 
C

Christine

Hi, here it is, its driving me crazy...


Under Format - Conditional Formatting on the tool bar:

Condition 1
Cell Value Is: equal to 1
Preview of fromat to use when condition is true: Red (is chosen)

Condition 2
Cell Value Is: equal to 2
Preview of fromat to use when condition is true: Yellow (is chosen)

Condition 3
Cell Value Is: equal to 3
Preview of fromat to use when condition is true: Green (is chosen)

Many thanks, C
 
D

Dave Peterson

My guess...

You are copying from a worksheet in workbookA and pasting into a worksheet in
workbookB.

But you have both workbooks open in different instances of excel.

If you close the "receiving" workbook and open it in the first instance (using
File|open), then copy|paste, you'll see that it works fine.

=======
If you doubleclicked on a file in windows explorer and it opened the second
instance, you can sometimes "fix" this behavior by:

Tools|Options|General|Ignore other applications (uncheck it)

--- or ---

Close Excel and
Windows Start Button|Run
excel /unregserver
then
Windows Start Button|Run
excel /regserver

The /unregserver & /regserver stuff resets the windows registry to excel's
factory defaults.

=========
I like just one instance of excel and open all my workbooks in that single
instance--it makes things behave "normally".
 
D

David McRitchie

Hi Christine,
That certainly works for me in Excel 2000, and if it didn't work in
other versions there would certainly be complaints. This is
strictly manual (not macros) copy and paste.

Even checking against what John Walkenbach wrote up as an
oddity (odd not in design, but a pain to work with).
The elusive Formula1 property for conditional formatting
http://www.j-walk.com/ss/excel/odd/odd07.htm
I just went through that mess to get conditional formatting into
my generation of HTML coding from a selection of cells.
As far as I know there is no connection here.

Would you also check the cell(s) at the destination, that they show
the same conditional formatting and colors in C.F. definitions.
Is calculation turned on, and to the cells test as numbers.
What happens if you hit F2 then enter. =ISNUMBER(K2)
 
C

Christine

Actually doing this all in the same workbook. Copying from worksheetA to
worksheetB. I just get the numbers not the conditional formatting.. I
tried paste special, all...
 
E

Earl Kiosterud

Christine,

How very odd.

First of all, let me check some assumptions on my part:

1) The CF you've described is in the original (copied cells, sheet A), but
doesn't function in the target (pasted, sheet B) cells.

2) Doing FOrmat - Conditional Formatting in sheet B after the paste shows no
conditional formatting set up? Or maybe does show it, but it doesn't work
for some reason? Which?

3) You have stood up and turned around three times. This is sometimes
necessary to get CF to work. No one knows why this is.

Try this with some test data in a new workbook. Your workbook may be
damaged.
 
C

Christine

Hi, When I paste it does past the numbers bit no colors. When I add new
numbers no colors appear. Very strange.. I will try the turn around three
times method to see if that works.. :)
 
D

Dave Peterson

After you pasted into the other worksheet and with that cell still selected,
what do you see when you do Format|conditional formatting?

(You skipped Earl's second question.)

Were you eating Cheetos at the time? If so, were you using your right or left
hand? Try using the other hand.

(I've never had any luck with the standing up and turning around 3 times tip!)
 
C

Christine

Hi Guys,
We Got it!!!!

Cheerios helps.. But also the document needs to be changed under
Tools-Share Workbook - deselect "allow changes by more than one user at a
time..

Once I deselected that option it pasted correctly. Then I re-enabled the
sharing.. Wow could this be any more complicated.. :)

Thanks for all your help..

-Christine
Click to expand...
 
D

Dave Peterson

Thanks for posting back.

But this is documented in Excel's help.
Look for: Features that are unavailable in shared workbooks for a big list.

But I very rarely use shared workbooks--so I never remember to even consider
that!
 
Top