Write to multiple cells in color

richh

Board Regular
Joined
Jun 24, 2007
Messages
245
Office Version
  1. 365
  2. 2016
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?
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
Depends on how the rest of your macro is written. If the output cells are updated in a loop, then something like:

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

You can also use a larger range, like:

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

    Range("A1:A50,C1:C50").Font.Color = vbRed
 
Upvote 0
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!
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,223,099
Messages
6,170,107
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top