Write to multiple cells in color

[richh]

I have a spreadsheet that will be filled in programmatically. I'd like the cells that will be filled in this way to be written in an alternate color. I know that I can do something akin to:

Range("A1").Font.Color = vbRed

However, I don't want to code 90+ lines of color changes. Is there a way to change all of my outputs to red?

 

[Eric W]

Depends on how the rest of your macro is written. If the output cells are updated in a loop, then something like:

    For r = 1 to LastRow
        Cells(r, "A").Font.Color = vbRed
    Next r

You can also use a larger range, like:

    Range("A1:A100").Font.Color = vbRed

    Range("A1:A50,C1:C50").Font.Color = vbRed

 

[richh]

Unfortunately, most of the cells are not congruent; they're laid out sporadically around the spreadsheet so a range wouldn't work out that well. I suppose I could just change the color of the cells to begin with, but that takes the fun out of it!

 

[Eric W]

You could save all the values in the sheet (MyData = Range("A1:Z100").Value), do your processing, then check the values again and anything that's changed, change the font color. That would be a fairly small loop. Not extremely efficient though.

Contrariwise, you could use copy/paste when populating a cell instead of just setting a value. Copy/paste will copy the cell format as well.

Or every time you update a cell, add it to a range {Set MyRange = Union(MyRange, Cells(r, "W"))}, then set the larger range all at once {MyRange.Font.Color = vbRed}.

It really just depends on the rest of your macro.