I'm building a grid which needs to have the cell formatted based upon the value.
Sheet 1 contains a grid of Service Providers (in Column A) and Customers (in Row 1).
The values indicate the level of the relationship between the customer and the service provider.
The formatting is based upon another sheet which contains the priority level and the level is formatted as you see in the image above.
I'm using VBA to read the priorities and to format the cells.
Initially, I was using the PasteSpecial(xlFormat) mechanism, but it was horrendously slow for 80,808 cells.
I tried just copying the bold, color and interior color, and that was still quite slow.
My idea is can I do the following.
1 - Iterate the priority levels on the second sheet.
2 - Select all cells in the first sheet with the same level (0 and blank are to be treated the same).
3 - Apply the format of the cell in the second sheet to all the cells in the first sheet.
In my mind this should be REALLY quick to run as there are only 7 different levels.
But I don't know how to select all cells with a specific value.
I've included the small sample with the second sheet here : Formatting.xls
I can't use conditional formatting as that only has 3 levels and I'm using Excel 2003.