Conditional formatting alternative

[huddsterrier]

Hi,

I have an excel sheet that is designed as a map of my warehouse layout, I have 3 conditional formats run by a macro doing a countif calculations on 4800 cells using data from another worksheet to change the colour depending on the amount of activity in that given cell.

The sheet runs like and absolute dog and just opening the sheet & scrolling up and down can be an absolute nightmare. Is there any way I can make the cells change colour without using conditional formatting? The only other way I think I could do it would be to put a formula in each cell, which I’m sure wouldn’t speed anything up or use a macro but then I’d have to reference each cell, unless there is a way I could do a blanket macro to cover all cells.

I’m at a total loss now in my quest to speed this sheet up and any help would be greatly appreciated.

 

[My Aswer Is This]

We can maybe provide a macro that would work.
Please provide more details about what your wanting.

Color what cells what color and what would activate the script.

 

[huddsterrier]

Hi,

The way the sheet works at the moment is I have the main sheet (named 'Map') which is columns C-CD, rows 1,4,5,8,9,12 following that sequence down to row 120. each of the cells has a text string in to identify it as a location.

I then have 2 sheets of raw data that link to pivot tables on a 4th sheet, named 'Pivot' (data in column B for 1 macro and F for the other 2 macros), the 3 macros set out the criteria of the conditional formatting.

the CF does a countif on the pivot table and changes the colour depending on the citeria.

The most common one run is =>1 then Green, =>3 Yellow, =>5 Purple (wont show red) any 0's remain white.

 

[My Aswer Is This]

What is your overall objective?

Are you just trying to change a cells interior color if some criterial is met?

I never use PivotTables so know nearly nothing about them.
Is the pivot table only being used to change a cells color?

I do not believe you would need three macros and pivot tables and CF to just change a cells color.

Give me the criteria on changing a cells color and what columns are we working with an what is the sheet name?

 

[huddsterrier]

The pivot table's are there to sort out the raw data.

I have the main sheet called 'Map' and the pivot tables called 'Pivot'.

on 'Map' the columns C-CD, rows 1,4,5,8,9,12 following that sequence down to 120 (add 3 add 1) have the reference data in them, i need it to count how many times that reference data is in the 'Pivot' sheet Column B.

If it's there 1 or 2 times then Green, 3 or 4 Yellow, 5 or more Red.

the other 2 macros looked at column F which had different data and different conditional formatting criteria, thats why I had 3 macros.

It would make alot more sense if you could see the sheet layout

 

[My Aswer Is This]

This is beyond my knowledgebase.
I will continue to monitor this thread to see what I can learn.