“I have a simple Excel sheet with a list of to-do items down the left hand column. I have clients along the top. For each employee, I have designated a color and have shaded the inside cells showing who is responsible for each item for each client. I put an “x” in a cell when a task is finished.
“Is it possible to set a formula that says “any time an ‘x’ is entered in a cell, it will change to a predetermined color?” This would make it much easier to see what needs to be done at a glance.” — Lee Z.
As you add staff, you’re going to run short of good colors. It will be more difficult to keep the various colors in mind. And it will become more tedious to assign each color pattern by employee.
Also, with your current solution, you can’t see which employee had been assigned a task after it’s been completed. This is information that you probably would find useful at times.
Each employee has been assigned a one-character code, as shown in the Employees section. Then, when you enter the code for each task in the left column beneath a customer’s name, the cell to the right automatically displays the appropriate color.
(You could use longer employee codes, of course. But the longer the code, the more time it takes to enter and the greater the chance of a data-entry error.)
As before, when you enter an “x” into a colored cell, it changes to black, as shown here.
To set up this display, first enter the text and format the borders as shown.
The customer names do not use merged cells, which I try to avoid wherever possible. Instead, they’re aligned using Center Across Selection.
To set up these labels, enter the customer names in cells B2, D2, and F2. Select the range B2:G2 and choose Home. In the Alignment group, choose the dialog launcher, shown here. In the Alignment tab, in the Horizontal dropdown list box, choose Center Across Selection. Then choose OK.
Assign the color codes to your employees. To do so, choose the Home tab. Then, in the Font group, use the Fill Color icon to fill each cell as shown. (I know, the Fill Color icon has nothing to do with fonts. But then, neither does the Borders icon, which is in the Font group as well.)
You now can assign a conditional format for each employee. To begin, select the range C3:C6, as shown here.
But when you make this selection, remember one critical thing:
Carefully note your active cell.
The active cell is important because you must enter all conditional formulas that use relative or mixed references from the point of view of the active cell.
Let’s review some terms here:
- An absolute reference is like “$C$5”. No matter where a formula is used, it always refers to cell C5.
- A relative reference is like “D3”. If the active cell is D4, then D3 refers to the cell immediately above the cell that uses the reference. But if the active cell is E3, then D3 refers to the cell immediately to the left of the cell that uses the reference.
- A mixed reference is like “M$8” or “$B6”. It includes both an absolute and a relative reference.
Therefore, when you enter a cell reference, any row number or column letter that doesn’t have a “$” in front of it is relative to the active cell.
With the range C3:C6 selected, and with C3 the active cell, choose Home, Styles, Conditional Formatting, Manage Rules. In the Conditional Formatting Rules Manager dialog, choose New Rule. 
In the New Formatting Rule dialog, choose “Use formula to determine which cells to format.” When you do so, Excel displays a dialog like the one shown here.
Enter the following formula in the edit box labeled: “Format values where this formula is true:”:
=B3=”b”
(Remember, our active cell is cell C3. Because cell B3 is immediately to the left of C3, this formula causes each cell in your selection to look one cell to its left for the value it uses for conditional formatting.)
Choose Format to assign the cell pattern for Bob, the employee with the “b” code. After you assign the color pattern, choose OK to return to this dialog, then choose OK again to return to the Conditional Formatting Rules Manager dialog. Your dialog now should look like the figure below.
Continue this process for each employee. As you add a rule for each employee, it will be added to the top of the list in the Rules Manager. Finally, when you’re done with your employees, you have one more rule to add: the rule that handles the “x” when a task is done.
To add this rule, choose New Rule again. But this time, in the New Formatting Rule dialog, choose “Format only cells that contain”. The first dropdown list box says “Cell Value”, which is what we want. Change the second dropdown to “equal to”. In the third box, which is an edit box, enter:
=”x”
Then choose Format to assign the black background color for completed tasks. Choose OK, and then OK again to return to the completed dialog shown below.
At this point, you might wonder about precedence. That is, we now have two rules that affect each cell. One rule sets the employee color and the other rule turns the cell black. How do we know which rule will win?
The Rules Manger dialog, shown here, determines the precedence. The rules at the top of the list win out over the rules that are below them. Because we created the “x” rule last, it naturally appeared at the top of the list, and therefore takes precedence over all other rules.
To change the precedence of a rule, click on it and then click on one of the two arrows to the right of Delete Rule near the top of the dialog above. Each time you click on an arrow, the rule changes position in the list, changing its precedence.
To complete your project sheet, copy the range C3:C6 and paste it to cell E3; then paste it again to cell G3.
Finally, one last thought. When you edit a rule, you’ll make fewer errors if you select the correct range and active cell. The range you need to select is shown in the “Applies to” column of the Conditional Formatting Rules Manager above. And the active cell always needs to be the first cell that appears in that column for the rule that you’re changing.
