Looking for a faster way to change the background color of a bunch of cells

[Ed_Collins]

Greetings,

I have a spreadsheet which contains a few hundred (or more) colored cells.

Within my spreadsheet I have an different tab, called my User_Options tab, that allows the user to specify the exact color and shade of these cells. Once they do, and once they switch back to the main tab, all of the cells with this particular background color change to the color they specified.

It's working fine... no problems... but it's slowwwwww.

I'm using a simple, double FOR NEXT loop. For example:

Please Login or Register  to view this content.

Is there a way to recode this, so that it's much faster? The cells that contain the "x" that need to be changed are all over the place, and in no order. If I knew ahead of time that I would be changing every cell in a particular row, I could select the entire row all at one, and change it all in one shot. But I don't know that.

Thanks in advance.

[millz]

What other values are there if they are not "x"?

[HaHoBe]

Hi, Ed_Collins,

try a For Each on the whole range in geting it all done in one Loop if you want to change each individual cell or make use of WorksheetFunction.CountIf to see if any row/column meets the criteria. Or utilize Find to set an object to the cell and work with it.

Ciao,
Holger

[Ed_Collins]

What other values are there if they are not "x"?

They aren't even "x". That's just an example.

The cells are actually one of four different values. Let's call the values "w", "x", "y" and "z". The user can change the background colors of all of these cells, to suit their own personal taste, if they desire.

The FOR NEXT loop works... it looks at every cell and if the background color needs changing, it does it. But the program noticeably slows down as it goes through this FOR NEXT loop and changes the cell colors. (And yes, ScreenUpdating is false.)

[Ed_Collins]

try a For Each on the whole range in geting it all done in one Loop if you want to change each individual cell or make use of WorksheetFunction.CountIf to see if any row/column meets the criteria. Or utilize Find to set an object to the cell and work with it.

Yea, that's the problem. I don't know how to code that. I don't know how to check the whole range and only change the cells needed. I only know how to check each cell individually, as shown.

[millz]

Try something like this? where "x" is one of the four values. I am assuming you don't have any blank cells within the range.

Please Login or Register  to view this content.

[HaHoBe]

Hi, Ed_collins,

assuming htat the first row holds the header and Columnn A is filled throughout:

Please Login or Register  to view this content.

@millz:

you´re sure there are no empty cells in the range before your macro and that the value will not be needed later on as well?

Ciao,
Holger

[millz]

@millz:

you´re sure there are no empty cells in the range before your macro and that the value will not be needed later on as well?

Hi HaHoBe, I don't quite understand what you mean.

Having blanks or not was what I first asked OP, and OP said there's 4 possible values. Also, I did not assume the value will not be needed later. And your code is highlighting the entire row within the range if any "x" exists within that row. OP only wants to highlight individual cells I think.



@Ed_Collins, if your data consists of blanks and the 4 possible values, you can try this:

Please Login or Register  to view this content.

[Ed_Collins]

milz, in my informal timing test, your code runs in less than half the time as my code. Thanks. That's a good start and might be all I need. (I figured there must be some way to select the entire range, but I didn't know how.) I will play around with this for a bit and experiment some more. Thank you for taking the time to respond.

HaHoBe, I will also test your code and see if it works (which I'm sure it does) and to see how much faster it is, compared to my code and to that of milz. Thanks!!

[HaHoBe]

Hi, Ed_collins,

you woiuld need to excahnge the equation fopr the worksheetFunction to meet the number of used cells in that row/column and may skip to the next row/column.

...in less than half the time

It should be a lot faster. You might consider to turn Calculation to Manual at the begin and turn in back to Automatic after the code.

Ciao,
Holger

[millz]

This might speed up a small fraction more.

Please Login or Register  to view this content.

Edit: And also consider the suggestion by HaHoBe to switch off/on calculation.

[romperstomper]

If all the cells have the same formatting, why not use a Style then all you have to do is modify the Style properties.

[kalak]

hi Ed,
How does this one work for you?
Blanks no matter. Styles no matter.

Please Login or Register  to view this content.