Excel - color cells based on other cells color

Question

I'm making a schedule in excel for our companys internal planning. I have sheets with teams, people and projects and in one sheet I have the overview and planning.

In the planning every event gets an area of 1 column 11 rows where I have made it so the project information is filled in automatically based on the first cells value (project number). Now the tricky part is that I would like all 11 cells to automatically change fill color based on the project number.

I want the color to be the same color as the matching cells in the project list sheet.

So something like :

• On sheet change
• check first cell for project number
• find that project number in project list sheet
• get that cells color
• fill all 11 cells with that color

and if project number is the text "empty" remove background color

Is this possible to accomplish?

Also when I change the color in the project list I want the color in the planning to update.

Any tips is appreciated, thanks.

Answer 1

You should be able to use conditional formatting to meet this requirment.

In the Home ribbon in Excel there will be a Conditional Format button, if on this you select Manage Rules and then Select New Rule,and then select Use a Formula to determine which cells to format.

The following formula can then be entered into the field

=$A$2=1

Where A2 equals the cell than contains the project number and 1 is the number for that project.

You can then select the format option and set the colour for the cell.

Once applied to one cell you can then select this cell and copy and paste special the format of this cell to the range of cells that you require.

You can then create additional formulas for the other projects