conditional formatting 15000 records. possible?

>at cell A1,> conditional formatting> use formula: type $AD1 = 1, then apply your colors.
new rule: type $AD1 = 2, then apply your colors.
new rule again to apply all your condition.
then you drag A1 to down till 150000.


Sample Conditional Format.xlsx <-- Attach a sample file for you, hope it help

Originally Posted by Bobbo Jones

sorry mate I don't think you completely understood, I know how to do the conditional formatting, my problem is that once I have done it, it slows the spreadsheet to a crawl.

Okie, then you want write a macro, then apply the color rather than conditional formatting?

found at http://endorkins.com/2009/07/13/slow...heet-try-this/
Duplicate Conditional Formatting Rules:
Conditional formatting can be a processor-intensive task if your rules aren’t set up right. Manage your conditional formatting rules (2007) via the ribbon at Home > Conditional Formatting > Manage Rules… Try to consolidate similar rules into one rule (e.g. a rule for an entire column instead of a rule for each cell in the column) eg: specify the range =$A$1:$A$15000

Conditional formatting formulas are recalculated each time anything calculates.
Perhaps macro, is better way for 15000 columns.
Hope it helps you

simple way to test that out
save a copy (just in case you accidently save over your work )

take all conditional format out and see if it still lags

Please Login or Register  to view this content.

If you wish update the colors all the time, suggest set a shortcut key. Macro > View macros > select "conditional_Formating" > options

Originally Posted by Bobbo Jones

I wonder if it is NOT actually the conditional formatting that is slowing me down?
the reason i think this, is that excel lags EVEN when im not changing anything. I just want to click a cell, and it lags. I wait, then I want to scroll down to look at a different cell, it lags again. NOTHING should be calculating when i do this.

Maybe you have too much formula inside the workbooks. try to reduce it might help you

What i found. Hope it helps you

Many things can slow response in Excel.

-Memory Leakage in GDI (graphics device interface) Resources (#GDI)
Multiple Excel windows, window zoom not set at 100%, lots of graphics, colours, formats, charts, images, ActiveX-objects. A combination of these will eat excel's graphical memory, which is allocated independent of available cards, ram, speed. Printers and fonts can also be involved.

-Files in temporary directory
Slow Opening of Excel files is frequently caused by a large number of files in temporary directory. To clear out the temporary directory you can get to it from your Start, Run by typing in %TEMP% into the box.

-Too many cell formats.
A format in this case means each combination of font characteristics. i.e. Font type, font size, font colour, interior colour, bold, underline, pattern, number format, height, width, etc. Reduce as much as you can. Eliminating unused custom cell formats might also help.

-Volatile Functions cause recalculation to take longer.
User Defined Functions that have had the volatile option added. But Excel itself has volatile functions: CELL() OFFSET() TODAY() INDIRECT() NOW() INFO() RAND() as reported in MS KB Q274500 which indicates Excel prompts you to save changes to the workbook when you close it and sometimes (bug) CELL() is not being treated as volatile and requires F9 (calculate).

a way of checking how much memory Excel is using with a worksheet formula.
=INFO("memavail") Amount of memory available, in bytes.
=INFO("memused") Amount of memory being used for data.
=INFO("totmem") Total memory available, including memory
already in use, in bytes.
=INFO("numfile") Number of active worksheets.