Forum Discussion
Solved
Change Color
Hello,
I'm in need for your help. At the moment, I'm trying to make a file with the whole years planning. So far so good, but I've stumbled upon a problem. I hope you can help me.
What I need is:
A manner (probably VBA) that allows me to automatically change the background color of a cell, depending on the background color of another cell.
For example:
Imagine a file with 2 tabs in it:
1. The "basic" tab
2. The "week"tab
Now, the two tabs are identical. If I where to change the text in cell A1 of the basic-tab, it changes the text in cell A1 of the week-tab. But, what I also need for it to do, is change the background color of said tab. If I change the backgroundcolor in cell A1 of the basic-tab, it needs to change the backgroundcolor in cell A1 of the week-tab.
Thanks in advance!
I see the following patterns:
- colors are applied to cells with numeric values only, cells with "v" don't have special colors.
- each row has its own unique highlight color
- you already have two conditional formatting rules to color cells based on their value "v" (light amber) or "ab" (pink)
You say that the colors relate to projects and that projects could change on a weekly basis.
You could insert a column that specifies which project currently applies to the person. Then you can use conditional formatting and set up rules to format each cell with the project color if it has a number. Now you don't have to manually set the project colors and can apply the same conditional formatting
logic to the other sheets.
For example, insert a new column B after column A for the projects and enter the project names, in my example they are Project RED, Project AMBER and Project GREEN. Now create three additional formatting rules with formulas. The three formulas are (Starting with cell C4 and applied through to X6:
=AND($B4="Project RED",ISNUMBER(C4))
=AND($B4="Project AMBER",ISNUMBER(C4))
=AND($B4="Project GREEN",ISNUMBER(C4))
Insert the same column and conditional formats in all your sheets and get the project value from column B with a formula, like you do with the other values.
Now you can change the project values in column B and the colors will change accordingly.
23 Replies
If you use conditional formatting instead of manually changing the cell color, then you can apply the same conditional formatting rule to other cells on other tabs.
Color is not data. Manually coloring cells means that the "logic" for the color is in your head, not in the spreadsheet. Find a way to put the logic into the spreadsheet, then it can be used by Excel.
Thank you for responding Ingeborg,
If I want to use conditional formatting, I need to have different data in two cells. If I have two cells with "2.75"in it, Excel won't know what colour to give it, but one. So for that to change, I would have to put a letter in it, but that is the one thing we want to avoid. Just numbers, and they need to be recognisable by colour.
Jonathan, that's where you need to think about your data architecture. You as the person in front of the monitor "know" that "this" value should be red and "that" value should be green, so you set the color manually. If you take away the human intervention, then the colors don't have meaning that is based in the spreadsheet.
Can you post a small data sample of what you have in place right now, using dummy names and dummy numbers? Explain why things have different colors. Then we can try to work out how to make that work with data instead of only manually set colors.
Jonathan, if your tabs are really identical you may select them (not data areas, but tabs itself) and work with them as with one tab. All your changes, includes colors, will affect cells in both tabs then.
If they are not identical when yes, you may change the property (what the colour is) of one cell base on the property of another cell only using VBA or third-party tools. But i have no ready to recommend decision here.
Thank you for responding Sergei.
At first all the tabs are the same, but sometimes I need to make a change in one tab, and for that tab only. Now, if they are linked through a formula, that's possible. But if they are linked by saying they all should be the same all the time, my file will be "smudged"once I make any changes in any tab.
Yes, with formula that's much more flexible. On the other hand, connect/disconnect tabs that's only one click, if 95% of cells are the same and that's not everyday routine task, i'd prefer this manual approach. It depends.
