Conditional formatting keeps disappearing - help required

[Forum freak \(at work\)]

Hi

This is has never occured to me before and after several tries I am
convinced it is nothing to do with my input.

I copy and paste a block of cells which contain conditional formats then
check that the pasted cells have the formatting. Everything is Ok so I
close the file. On re-opening I test the cells and find the first 7000ish
rows are ok but after that the formatting is missed from random cells.

The aim is to have 28550 rows with conditional formatting on columns B to
I - Can Excel cope with this amount?

I recorded a macro to copy 81 rows and paste the info in the cells below
then save. I checked the formatting and everything was OK after I closed
the file and re-openend. I then ran the code from a macro button (many
clicks later) and occasionally checked the formatting. Everything seemed OK
but when I closed the file and re-opened I have lost a lot of the
conditional formatting. As I had included a "save" within the macro I
thought It would have been ok.

I suspected this old work PC to be underpowered at 1595Mhz and 768mb
physical memory so I tried it on my AMD 6400+ with 2 Gb ram and still had
the problem.

Can anyone shed any light onto the reason for the disappearing
formatting!?!!

Kenny W
XP pro
Office 2003

 

[Argy]

Kenny:
I just signed into this site and by looking around, I found your message.
Well if I understand correctly, you are using the conditional formula which
in Excel 2003 only take up to three conditions. Ok once you define the
conditions, you should copy and paste this condintion down to the row you
need. I create the conditional in Excel 2003 and then open it in both, Excel
2003 and 2007 and they are working. I guess that maybe in your Excel you may
have some predefined restriction that may be controlling the way this is
working in your computer.

My suggestion would be to start over and do it by copying and pasting the
format, not by using the Paste Special format; although I didn't tried it at
this time, I know it also works, but for your security, just use the one I
said. Wether you are interested in looking at the file I created, I can send
it to you, if you ask for it. By the way, the try I did was for a formating
copied in row 4 and copy and paste up to line 35000. I hope this help.

Argy

 

[Forum freak \(at work\)]

Hi Thanks for looking

My conditional formatting is
Cells D5:H10 Formula is =And(G5<>"",H5="") ....true shades cell grey

in addition to this another conditional format is applied to cells B5:I10
Formula is =F10>$E$1

In addition to this cells D5:H10 has Data Validation, custom formula =C6<>""

these are applied down to row 70 (slight variations to cell references) then
this is copied to the next row(365 times) as it is a diary.

I have tried copying ALL as well as just copying formats as you suggested
but it did not work.

When fully copied to rows in excess of 28000 the file size is over 3 Mb
however I could send you the top 84 rows ready formatted (file size is 76kb)
It has macros but I could strip them off if you prefer.

Would you like to see the file or can you understand from my notes above.

Regards
Kenny

 

[Argy]

Kenny;
All my pleasure. I hope I can help. Please send me the file to
(e-mail address removed)

About the memory. This is one of the big issues with Microsoft applications.
Workbooks apparently grow in memory geometricaly when you copy down formulas
or conditional formts, and others. It looks to me that Microsoft has this
fear or control freak habit and then try to keep in memory everything. So
what happens is that when save the file, it appears that the file not only
saves the current physical formulas, or formats, but also its memory
representation. In other words, it looks that it assumes that the file has
all this memory used, but it is not. A way I have found to control this is
reopening the file and re-saving it. Do a test by copying a large number of
formulas (30 colums with any semicomplex formula) and copy the whole row down
to 15,000 rows. Save it and check the size. Open it and save it again and
check the size again. You migh find this interesting.

Ok, send me the file and I will try to find your solution. Good luck.