Forum Discussion
Change Color
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.
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.
So here's an example of the file I want to use. The original file has about 200 rows. Furthermore, it contains one tab per week of the rest of the year.
So, a small summary:
I would like to be able to make all the tabs to be the same as the basic tab, including the colours. So if i where to change anything in the first tab, the rest should follow.
If I need to make a change in just one tab (except for the basic tab), I could just delete the formula by entering any value and change the background colour (for just that tab, the other tabs need not to change).
There already is a VBA in the original file, but I can't upload the file including macro's.
The VBA allows me to count the value's of coloured cells, as well as the amount of coloured cells (with the same colour).
The VBA:
Function ColourFunction(rColor As Range, rRange As Range, Optional SUM As Boolean)
Dim rCell As Range
Dim lCol As Long
Dim vResult
lCol = rColor.Interior.ColorIndex
If SUM = True Then
For Each rCell In rRange
If rCell.Interior.ColorIndex = lCol Then
vResult = WorksheetFunction.SUM(rCell, vResult)
End If
Next rCell
Else
For Each rCell In rRange
If rCell.Interior.ColorIndex = lCol Then
vResult = 1 + vResult
End If
Next rCell
End If
ColourFunction = vResult
End FunctionIf you can help me, I would be grateful!
By the way, each colour represents a specific project. There are 3 different projects in the file, so 3 colours. Sometimes we need to change the project for a person for just 1 week, so that's why the tabs should only follow the basic tab, for when a person changes it's entire roster.
Okay, so here is the file.
The idea is to do most of the work in the "basis" tab. You only work in the weektab when someone calls in sick, or doesn't show up, or changes projects for just that week.
Each color defines a project. The "v" stands for "vrij", thats the Dutch word for: Free, meaning he doesn't work that shift.
Now, if for example John is going to work a different project starting week 2, I would like to just adjust the color in the "basic" tab, in stead of changing it in every tab by hand.
If I need to change something in a weektab, I can just write over the formula, so only that cell changes. This doesn't work when I link the tabs.
I'm not the only one working in this file, so in my opinion, the formula works best, or purhaps using automatic formating, but I don't know how to use that in a complex manner.
There is already a VBA in there, but I can't upload the file with macro's, so I deleted the cells that where using the macro (I only used it to count the value in cells with identical colours).
The VBA:
Function ColourFunction(rColor As Range, rRange As Range, Optional SUM As Boolean)
Dim rCell As Range
Dim lCol As Long
Dim vResult
lCol = rColor.Interior.ColorIndex
If SUM = True Then
For Each rCell In rRange
If rCell.Interior.ColorIndex = lCol Then
vResult = WorksheetFunction.SUM(rCell, vResult)
End If
Next rCell
Else
For Each rCell In rRange
If rCell.Interior.ColorIndex = lCol Then
vResult = 1 + vResult
End If
Next rCell
End If
ColourFunction = vResult
End FunctionThe file is in Dutch, so is the formula. If you are in need of translations:
ALS = IF
Thanks in advance!
Okay, so here is the file.
The idea is to do most of the work in the "basis" tab. You only work in the weektab when someone calls in sick, or doesn't show up, or changes projects for just that week.
Each color defines a project. The "v" stands for "vrij", thats the Dutch word for: Free, meaning he doesn't work that shift.
Now, if for example John is going to work a different project starting week 2, I would like to just adjust the color in the "basic" tab, in stead of changing it in every tab by hand.
If I need to change something in a weektab, I can just write over the formula, so only that cell changes. This doesn't work when I link the tabs.
I'm not the only one working in this file, so in my opinion, the formula works best, or purhaps using automatic formating, but I don't know how to use that in a complex manner.
There is already a VBA in there, but I can't upload the file with macro's, so I deleted the cells that where using the macro (I only used it to count the value in cells with identical colours).
The VBA:
Function ColourFunction(rColor As Range, rRange As Range, Optional SUM As Boolean)
Dim rCell As Range
Dim lCol As Long
Dim vResult
lCol = rColor.Interior.ColorIndex
If SUM = True Then
For Each rCell In rRange
If rCell.Interior.ColorIndex = lCol Then
vResult = WorksheetFunction.SUM(rCell, vResult)
End If
Next rCell
Else
For Each rCell In rRange
If rCell.Interior.ColorIndex = lCol Then
vResult = 1 + vResult
End If
Next rCell
End If
ColourFunction = vResult
End FunctionThe file is in Dutch, so is the formula. If you are in need of translations:
ALS = IF
Thanks in advance!
Jonathan, not exactly. You may have two cells with the same values in them, but apply to them different colors based on the logic depends on combination of values in other cells (e.g. combination of column and row name, whatever) - rules in conditional formatting could be quite complex.
