Set a Cell Background Color to Another Cell’s Color

[Jacolene]

Hi All,

My default background colour for the workbook is R0 | G36 | B52.

I would like to change the colour of the entire workbook and any additional tabs that might be added by only changing the colour of C6, which is for all headers.

I am currently using "range("Notes!A1").Interior.Color = range("C6").Interior.Color", which is working fine, but the issue comes in if a row is added, it is still updating "Notes!A1" and not "Notes!A2" for example.

So I am thinking that it shoul "Look up for the default color (R0 | G36 | B52), throughout and change to the selected color in C6?

I have attached the sample workbook.

Thank you in advance.

[LJMetzger]

Hi Jacolene,

a. As you have noticed, when you change a row and/or column VBA code does not adjust.
b. You need a way to uniquely identify the cells you want to treat specially. Methods to do this include:
(1) Background Color
(2) Text Value
(3) Location of the cell in relation to some known cel
(4) Font Characteristic (e.g. Name, Size, Bold, Italics, …)
c. In this case it seems like 'Font Color' is a good choice.
By changing one RGB index a small amount, we can generate a different color number that the human eye can't differentiate.
d. There is nothing wrong with merged cells, but they are occasionally difficult to work with using VBA.
In cases like yours, when it looks like you are only using one row and want to center the text, usng 'Center Across Selection' Formatting may be better suited (for future reference)
e. It looks like all your 'special cells' are constants (not formulas) so that makes them easier to work with.
f. I created 3 Main Macros to do what your want:
(1) aaaChangeFontColorOfSelectedCellsToRGB255_254_255() changes the Font Color of the Selected Cell or Cells (can be run using Alt F8)
(2) IdentifyAllOffWhiteFontsInWorkbook() Identifies all Cells that will Change Background Color (see Yellow CommandButton Shape on Sheet 'Edit')
(3) ChangeBackgroundColorforAllCellsWithOffWhiteFontInWorkbook() changes the Background Color of Cells with 'Off White' Font Color (see Yellow CommandButton Shape on Sheet 'Edit')

Current Font Color: vbWhite = 16777215 = RGB(255, 255, 255)
New Font Color for Selected Cells: 16776959 = RGB(255, 254, 255)


See the attached modified copy of your sample file which contains the following code in Ordinary Code Module ModChangeCellBackgroundColor:

Please Login or Register  to view this content.

To prevent typos from ruining days and weeks of work 'Option Explicit' is NEEDED at the top of each code module. This prevents errors caused by missspellings and FORCES every variable to be DECLARED (e.g. Dim i as Integer). http://www.cpearson.com/excel/DeclaringVariables.aspx

Lewis

Attachment deleted due to runtime error. See file associated with post #4 in this thread for corrected attachment. LJM

[Jacolene]

Hi Lewis, thank you so much for the in depth explanation.

When I ran the below 2 Macros, I received an error message: Invalid use of Null - Ln83 Col9 (iColorRGB = r.Font.Color)

• ChangeBackgroundColorforAllCellsWithOffWhiteFontInWorkbook
• IdentifyAllOffWhiteFontsInWorkbook

[LJMetzger]

Hi,

I apologize profusely for the problems you had. I tested the software, but not nearly enough. I easily duplicated your error and found one other error:
a. Your problem was caused by a cell having more than one font color (on my Notes Sheet - color me embarrassed).
b. A similar problem occurred when a sheet was BLANK.

I fixed both errors.

See the attached corrected file that contains the following code (changes in red):

Please Login or Register  to view this content.

Thank you very much for the rep points.

Lewis

[Jacolene]

Hi Lewis,

Thank you, all seems to be working fine, but some "headers" with a background colour doesn't seem to change. for example on sheet "Guest Manifest" R1 CA.

It also doesn't change the background colour on new sheets.

Is there not a way where the Macro can search for the default background colour (R0 | G36 | B52) in the entire work book and change to the new colour, even if the font is black?

Workbook attached

Thanks in advance.

[LJMetzger]

Hi,

background colour doesn't seem to change. for example on sheet "Guest Manifest" R1 CA.

Error was corrected. Those cells used formulas which required a slightly different search technique.

Is there not a way where the Macro can search for the default background colour (R0 | G36 | B52) in the entire work book and change to the new colour, even if the font is black?

Added per your request.

It also doesn't change the background colour on new sheets.

I don't understand this one. You have to run the Macro again when new sheets are created, since the Macro does not currently run automatically.

Lewis

[Jacolene]

You are a star!