-1

I have 4 different cells, each on a different page in my Excel workbook. Each is a single-cell named region. I want these 4 cells to ALWAYS have the same matching background color. Can I use a macro to ensure that three of these cells always have the same interior.color as the 4th cell? That way whenever I make a change to the 4th cell it propagates across the other 3. I know how to write a macro that would make this happen anytime I actively run the macro (such as by attaching it to a form control button), but I want this color lock to just be inherent to the workbook...always in effect without me needing to trigger it.

If i were trying to match values, I would simple write "=A1" in the other 3 cells. How can i achieve the equivalent with regards to background color?

Dave F
  • 109
  • 2
  • 9
  • Do you have a limited number of colors in mind? Is there a value in the cell? – Shimeon Jul 17 '19 at 03:19
  • I do not foresee ever changing the color. No value. Locking the font size/style would be nice, but not mandatory. – Dave F Jul 17 '19 at 13:42
  • For each of the cells that I am trying to color-link, I will instruct users to paint the cell's format onto other cells via Excel's Format Painter. So these color-linked cells will have no value of their own, but they are the template for users to paint other cells with, including their background color and font type. (the reason for all of this is that I've already created a UDF that counts cells based on their background color, so i'm placing once of these color-linked "template" cells on each worksheet to ensure users use Format Painter to paint cells with the EXACT background color.) – Dave F Jul 17 '19 at 13:48
  • In this case, a better option would be to set the color to what you want and then protect the cells so that they cannot be changed by the user. Or, maybe create a macro that colors the cells and add it to a button. The user would press the button to color the cells rather choosing the color some other way. – Shimeon Jul 19 '19 at 00:49

1 Answers1

0

How about using the Worksheet_Change or the Worksheet_SelectionChange events to coordinated the colors across sheets?

This won't trigger immediately on the actual change of the color but it should update as soon as you change a value or cell selection (depending on which event you select).

For Example:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

'Remove the message box code and replace with your color change code.
MsgBox ("Cell " & Target.Address & " Selected")

End Sub
Shimeon
  • 243
  • 2
  • 9