how to speed up my macro, going very slow, row by row

[dmcgov]

i have this code that runs terribly slow. can someone help me with this?

Please Login or Register  to view this content.

basically i check for a white background and turn it to gray
and then i turn black text to white. but it is very slow. is this something for cells to use?

[romperstomper]

That is an awful lot of cells to check...

[dmcgov]

note, im trying to use Dark Mode with office. i can successfully change everything (that's a white bg color) to a dark gray. then i need to set the font color to white if its black. if i manually select cells and change the bg color, it's very fast. same with font color. is there a way to do this fast?

[dmcgov]

i tried this but still the same slowness:

Please Login or Register  to view this content.

[dmcgov]

so i got around the slowness by doing the following:

Please Login or Register  to view this content.

but when i do this, nothing happens, what am i doing wrong?

Please Login or Register  to view this content.

[CK76]

You don't need .Cells in front of interior color.

You can set interior color of entire range in one shot.
Ex:

Please Login or Register  to view this content.

However... do note that applying formats to unused range (i.e. range not holding values etc) could severely impact workbook performance.

As formats will tag every cell in worksheet as used range. This is terrible practice.

EDIT: Just tested, huh what do you know. It wasn't as bad as I had expected. Only increased size by about 1kb. And formula performance wasn't bad. Perhaps, uniform fill color & font color alone doesn't have huge impact.

[Richard Buttrey]

Stating the obvious but are you sure there are any cells with the colours you mention?

Are you able to upload the workbook?

[CK76]

Oh one more thing. If you check entire range's .Cells.Interior.Color, unless it's uniform color. It will always return 0.

[dmcgov]

thanks CK for that kernel of logic. didn't know that. so here is my code. the fill is set to gray and the statement works and is very fast (under 1 second). but now i have a problem with the font color not changing. see code below. will upload a

Please Login or Register  to view this content.

here is the file, just one sheet (sheet5) with code

[CK76]

In your sample, you are using Font.ColorIndex instead of Font.Color

As I've stated, when there is different fill color, or font color present in the sheet. It will return default value.

So for .Interior.Color it will return 0 in your sample and .Font.Color will return Null.

I'm not sure why you are checking entire range's property for each. It really gives you no control over individual cell's format.

Personally... I'd just set the entire sheet as desired. Then apply any other formats later.

Ex:

Please Login or Register  to view this content.

FYI to check for null value, you'd use IsNull(.Font.Color).

[dmcgov]

so some background on this macro, i have fallen in love with dark mode and i want this across all office products. that part was easy as it is done in one step. getting the body of a sheet to be the same, not so easy. i want a QAT button to turn on the dark mode for the sheets (and one to turn it off if i run into any difficulties). so if it is an unused sheet, then changing the fill and font color is easy. but what if i have an existing sheet that has various fills and font colors, then i would be changing the whole sheet and i don't want that. basically i want the sheet to examine the fill and font color, if a cells properties are no fill then change it to gray and if the font color is automatic, then change the font color to white.

does that help?

[dmcgov]

FYI to check for null value, you'd use IsNull(.Font.Color).

so i did this:

Please Login or Register  to view this content.

and it changed the font from red to white. i don't want that. if the font color is automatic (or null), then set it to white, otherwise leave it alone.

[CK76]

if the font color is automatic (or null), then set it to white, otherwise leave it alone.

This isn't possible when you do entire range in one shot. You have to iterate cell by cell. Which is very time consuming process.
Hence, my comment that it's not a good idea to do this

Alternative method is...
Create new sheet. Set up entire range using code provided for fill color and font color.
Then in original worksheet, iterate over each cell of used range and transfer format to new sheet. Once satisfied, you can delete the original.

Ex:

Please Login or Register  to view this content.

[Daishiknyte]

You're changing billions of cells. 16384 Columns x 1048576 Rows. Try limiting yourself to a much smaller range.

[Richard Buttrey]

You're changing billions of cells. 16384 Columns x 1048576 Rows. Try limiting yourself to a much smaller range.

....and the UsedRange would be a good place to start....

[torachan]

@dmcgov, surely the question has to be asked, why ?, as you will never see or use 99.9% of the sheet.
torachan.

[dmcgov]

so yup, after some consideration, i will try coding just the usedrange for the font color. I will post back once i have some code to work with

[dmcgov]

so here is the code that i have (based on user comments).

Please Login or Register  to view this content.

what i want is for rng to be activesheet.usedrange. that part is easy.

but for rng2 and 3, i want the unused columns and unused rows. how can that be done.

[CK76]

This...

Please Login or Register  to view this content.

[dmcgov]

Perfect, thanks CK, that did the trick.

[arslanca]

Bu sorun bende de var

[dmcgov]

Çözüm sizin için işe yaradı mı?