Cell Background Color

Question

I have a hairbinger of a problem here. I've been using Excel for years and now use 2010. To save strain on my eyes, I use a gray color for all windows instead of the default white. In my spreadsheets, I use cell Fill Colors to highlight certain data I have. The Fill Color I use is an off-white. By experimenting with Excel 2010, I found that color to have the following RGB values - R=230, G-230 and B=230. When I select Fill Color from the ribbon, my current spreadsheet shows RBG values as - R=255, G=204 and B=153. That is an off-orange color. But the cell background color shows is an off-white color in my spreadsheet

When I copy the cells to a NEW spreadsheet, the Fill Color is now showing as off-orange. Even if I use Format Painter to paint the cell from my old spreadsheet to a new one, the off-white background changes to an off-orange background in the new spreadsheet.

I have 56000 rows with 9 columns. About 40% of the cells use the off-white Fill Color.

How do I non-manually change all those cells with an RGB=255,204,153 to an RGB=230,230,230? I do not use or understand VB, scripts or programming.

Thanks.

Answer 1

I have a hairbinger of a problem here. I've been using Excel for years and now use 2010. To save strain on my eyes, I use a gray color for all windows instead of the default white. In my spreadsheets, I use cell Fill Colors to highlight certain data I have. The Fill Color I use is an off-white. By experimenting with Excel 2010, I found that color to have the following RGB values - R=230, G-230 and B=230. When I select Fill Color from the ribbon, my current spreadsheet shows RBG values as - R=255, G=204 and B=153. That is an off-orange color. But the cell background color shows is an off-white color in my spreadsheet

When I copy the cells to a NEW spreadsheet, the Fill Color is now showing as off-orange. Even if I use Format Painter to paint the cell from my old spreadsheet to a new one, the off-white background changes to an off-orange background in the new spreadsheet.

I have 56000 rows with 9 columns. About 40% of the cells use the off-white Fill Color.

How do I non-manually change all those cells with an RGB=255,204,153 to an RGB=230,230,230? I do not use or understand VB, scripts or programming.

Thanks.

You can do this through the Replace capability.  It supports find-and-replace of the cell format.  So, search for cells with a fill of (255,204,253) and replace with a fill of (230,230,230).

---

Tushar Mehta (Technology and Operations Consulting)

www.tushar-mehta.com (Excel and PowerPoint add-ins and tutorials)

Microsoft MVP Excel 2000-Present

Answer 2

Pressing Ctrl+m should not open the VBA window unless you have a break set (there is a purple dot in the left margin of the VBA code window. 

If you have accidently set break mode you can unset it by 1. clicking the purple dots in the margin (its just a toggle).  2. Or I suppose you could close and reopen the file.

Ctrl+m was just my suggestion for a shortcut key.  Here is how you assign a shortcut key to a macro

1. Activate the spreadsheet, not the VBA window
2. Choose View tab, Macros, View Macros.
3. Select, but don't run, your macro, click the Option button and enter a letter in the box beside Ctrl+
4. Click Cancel (its not going to be lost)

Regarding assigning m, you can really assign any lower or upper case letter.  However, all but 3 lower case letters are used by Microsoft (and some of the upper case letters also).  If you assign something like Ctrl+c your macro will run not Microsoft's copy command.  e, m and q are unassigned lower case letter at least if you are in 2010.  (unless Microsoft has sneeked one in I don't know about.)  Now the uppercase letter are a different matter, almost no one knows or uses the assigned ones so choose them is fine.  But it does mean that to run them you will need to press Shift+Ctrl+ the letter.

Regarding saving:  When you save a macro in the VBE window, you save the workbook it is in.  When you save the spreadsheet file you save any macros that are in it. I made the assumption that you had used macros or else I would have provided all the details, sorry. 

IMPORTANT:  If you want to keep the macro you need to save the file as Macro Enabled xlsm, not xlsx.

---

If this answer solves your problem, please check Mark as Answered. If this answer helps, please click the Vote as Helpful button. Cheers, Shane Devenshire

Answer 3

Thanks Tushar for responding tomy situation.

I followed your advice and here is what happens:

1. Selected cell with Fill Color I want to change
2. Clicked on Find & Replace button on ribbon, selected Replace, selected Options.
3. Clicked the Format button on the Find what row, selected Format, clicked the Fill Tab and entered the Fill Color I want to change - 255,204,153.
4. Repeated step 3 but used the Replace with row and entered the new Fill Color I want - 230,230,230.
5. Clicked Find Next button - NOT FIND ALL. The Find and Replace dialog box goes blank and after a few seconds comes back. It continues to do this for ever so I have to use Task Manager to shut down Excel.

Obviously I am doing something wrong here.

Can you help please.

Thanks.

Umm...not sure why it didn't work for you.  I did the same thing...well almost the same thing.  Instead of clicking Find Next, I selected Replace All.

---

Tushar Mehta (Technology and Operations Consulting)

www.tushar-mehta.com (Excel and PowerPoint add-ins and tutorials)

Microsoft MVP Excel 2000-Present

Answer 4

Thanks Tushar for responding tomy situation.

I followed your advice and here is what happens:

1. Selected cell with Fill Color I want to change
2. Clicked on Find & Replace button on ribbon, selected Replace, selected Options.
3. Clicked the Format button on the Find what row, selected Format, clicked the Fill Tab and entered the Fill Color I want to change - 255,204,153.
4. Repeated step 3 but used the Replace with row and entered the new Fill Color I want - 230,230,230.
5. Clicked Find Next button - NOT FIND ALL. The Find and Replace dialog box goes blank and after a few seconds comes back. It continues to do this for ever so I have to use Task Manager to shut down Excel.

Obviously I am doing something wrong here.

Can you help please.

Thanks.

Answer 5

Colors in 2007 and 2010 are much more complex than in 2003.  Were your old sheets 2003 or earlier?

For example if you record a color in 2010 it might look like this:

    With Selection.Interior

        .Pattern = xlSolid

        .PatternColorIndex = xlAutomatic

        .ThemeColor = xlThemeColorAccent5

        .TintAndShade = 0.599993896298105

        .PatternTintAndShade = 0

    End With

In 2003 it would look like this

    With Selection.Interior

        .ColorIndex = 33

        .Pattern = xlSolid

    End With

The RGB settings could be the same but themes and patterns and tints and tint pattern now come into play.  I don't kow if we can help but if you post the RGB values for your 2003 spreadsheet we might be able to figure out how to duplicate it in 2010.

I'm not absolutly sure I follow this:

"By experimenting with Excel 2010, I found that color to have the following RGB values - R=230, G-230 and B=230. When I select Fill Color from the ribbon, my current spreadsheet shows RBG values as - R=255, G=204 and B=153. That is an off-orange color. But the cell background color shows is an off-white color in my spreadsheet

Where did you get the 230, 230, 230 values?  2010 or...?

---

If this answer solves your problem, please check Mark as Answered. If this answer helps, please click the Vote as Helpful button. Cheers, Shane Devenshire