Too cell formats error

[HorseGoose]

I have an application to which I have added an option so the user can change the colours of the environment. There is a pallet down the side of the screen which is clickable and runs a proc. for changing the colours in the unlocked user entry cells of the application.

I understand the maximum number of cell formats in a workbook is 4000, but when changing from one colour to another the number of formats in the workbook will change as each sheet changes then return the original number of formats. Imagine you have two sheets both in blue. If you use code to change the colours you will change one sheet then the next so at one moment you will have twice as many formats before returning to the original number of formats when the colour has been changed on the two sheets.

Well my issue is this. I can change the colours using the code listed below, but after 10 differnet colour changes the system gives the error "too many cell formats etc etc". If it works changing one to another then it works, unless the changes are cumulatively held on a clipboard or something.

Can anyone help.

Cheers

HG


code
Application.EnableEvents = False
Application.ScreenUpdating = False

ActiveSheet.Unprotect "greatstuff"
ActiveWorkbook.Unprotect "greaterstuff"
Union(Range("D1:E5"), Range("H1:I4"), Range("H5"), Range("L2:L4"), Range("M3:N4"), Range("P9"), Range("P12") _
, Range("P17"), Range("P34"), Range("P36"), Range("P38"), Range("P4041"), Range("P4548"), Range("B52:B58") _
, Range("C63:C67"), Range("B9:M49")).Select
Selection.Interior.ColorIndex = colour
ActiveWorkbook.Protect "greaterstuff"
ActiveSheet.Protect "greatstuff"

Sheets("component").Select
ActiveSheet.Unprotect "greatstuff"
ActiveWorkbook.Unprotect "greaterstuff"
Union(Range("D26"), Range("G2:G4"), Range("K2:L4"), Range("C10:F33"), Range("H10:O33"), Range("D36:H39"), Range("H50") _
, Range("H52:H53"), Range("L49:M49"), Range("L52"), Range("M52:M55")).Select
Selection.Interior.ColorIndex = colour
Range("G5").Select
Selection.Interior.ColorIndex = colour
Range("G6").Select
Selection.Interior.ColorIndex = colour
Range("AO25:AO50").Select
Selection.Interior.ColorIndex = colour
Range("$D$2").Select
ActiveWorkbook.Protect "greaterstuff"
ActiveSheet.Protect "greatstuff"

Sheets("emulsion").Select
ActiveSheet.Unprotect "greatstuff"
ActiveWorkbook.Unprotect "greaterstuff"
Union(Range("E7"), Range("C8:E20"), Range("G8:I20"), Range("L6:O20"), Range("R6:R20"), Range("O24"), Range("O26") _
, Range("Q27"), Range("O28"), Range("Q29"), Range("O31"), Range("I34:I44"), Range("N34:N38"), Range("Q34:R36"), Range("C40:E44")).Select
Selection.Interior.ColorIndex = colour
Range("$C$8").Select
ActiveWorkbook.Protect "greaterstuff"
ActiveSheet.Protect "greatstuff"

Sheets("beverage formula card").Select
ActiveSheet.Unprotect "greatstuff"
ActiveWorkbook.Unprotect "greaterstuff"
Union(Range("L15"), Range("L17"), Range("L30:L69")).Select
Selection.Interior.ColorIndex = colour
Range("$L$15").Select
ActiveWorkbook.Protect "greaterstuff"
ActiveSheet.Protect "greatstuff"

Sheets("main").Select
Range("D1").Select
Application.EnableEvents = True
Application.ScreenUpdating = True

 

[CBasicAsslember]

Try doing a save between changes.

 

[abaldwin]

Also ran into this formatting problem with a very large sheet a while back.

As a matter of fact the retention by Excel will save each and every change in formatting between cells as one of your 4000-4600 combinations of cell formating.


If 2 sheets with bold letters and blue background, you make one change, now you have used two. Change it back before saving and yous till use 2.

Excel never lets go of these. Importing sheets into new workbook clears this up for a while as you are basically "Starting over" with formatting schemes in the new workbook.


Andy Baldwin

"Testing is the most overlooked programming language on the books!"

Ask a great question, get a great answer. Ask a vague question, get a vague answer.
Find out how to get great answers FAQ219-2884.

 

[CBasicAsslember]

http://support.microsoft.com/kb/213904

 

[HorseGoose]

This is helpful, thanks.

I guess the only way to make this work is after every 5 changes of colours make the applicatin save automatically, that then resets the formats in the mind of Excel, if I udnerstand you well.

I imagine nobody has found a VBA way to set the formats back to 0 in the mind of excel? (other than physically saving I mean).

Thanks a lot.

HG