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.
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.
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.
That works as a charm, thank you!
There's is one thing however, the VBA isn't working anymore. I think he doesn't recognize the background colour?
Do you have any idea how to sum up the value's of the cells for each project?
Yes.
The simplest would be to sum up each row in the weekly sheets and then use a lookup the result. For example if the row total in sheet "Week 1" is in column Y, but you don't know which row has which project, you could use something like
=INDEX('Week 1'!$Y$1:$Y$30,MATCH("Project RED",'Week 1'!$B$1:$B$30,0))
You could have the text of the project in a cell and refer to that cell, of course.
You can hide the column with the row totals in the weekly sheets if you don't want to see them.
I should have been more specific, I need to know the hours per project, per day. That way, I can monitor my progress and shift people to other project if needed.