Is it possible to have a conditional formatting cell "visually cycle" through all the formats that evaluated true?

Question

Like the title says, "In Excel, when a cell has multiple conditional formatting rules that evaluate true, is it possible to have the cell "visually cycle" through all the formats that evaluated true? If not, suggestions on what to do would be appreciated!"

I'm creating an employee schedule for a business that has multiple job areas that need to have an employee assigned to cover. The schedule is currently set up with the date on the top row, employee list down the left column, and the employee's assigned "job area" cross-referencing with the date on the top row. Originally it was set up where if every required "job area" didn't have someone assigned to it, the date would (via conditional formatting) change to red.

I've set it up now that if a condition isn't met, the date will change to the color of the "job area" that doesn't have an employee assigned to it. However, there are cases where multiple job areas don't have an employee assigned, but the date will only change color based on the first condition that isn't met.

It'd be nice if there was some way for the date cell to cycle through the different colors that correspond to the job areas where no one is assigned. I have a hunch that's not possible though.

If it is possible, I'd love to know how to do it. And if it isn't, if anyone has any suggestions on how I can modify the Excel sheet to make it easier to identify the job areas that don't have anyone assigned to them, I would appreciate it.

FYI This schedule goes out months in advance.

Answer 1

I don't believe there is a way to make a cell cycle through formats using Excel's native conditional formatting functionality (you can probably do it in VBA, but that seems overkill to me). However, there's a simple enough workaround:

For each rule, you can use different formats that aren't incompatible with one another. For instance, Rule 1 can apply a red font, Rule 2 can apply bold text, Rule 3 can apply a blue left border, Rule 4 can apply a red bottom border, and Rule 5 can apply a yellow fill. As long as each format is compatible with the others, you will be able to see exactly which criteria the cell met.

Example:

Answer 2

If you are using Excel 2007 or 2010 there are some clever things you can do with icon sets. You could assign numerical values to each job - missing tasks would set some threshold value and you could use a traffic light system to give a general indication of how many posts are not filled. There is also a 5 value icon set - little checkerboard squares which would give an immediate visual indication of up to 5 missing posts. Something else that works quite well (in a separate cell) is using something like a lower case l as a bar graph - each l being a missing post.

Answer 3

To visually cycle by a fixed time of like a second, you would need to execute a macro, like every second. Not only would this construct require quite an effort to build, it would most likely have a negative impact on the performance of excel. Plus, you basically have an interesting information, but you don't represent it in a reusable way.

I would not recommend this solution at all.

---

For me, cycling is out of the question. Bercause of that, you will need something else, which will eigther need space vertically or horizontally. There is no way around this, except using a combo-box, which basically folds your needed vertical space. But you will have to state each option somehow.

---

If it has to be by color, why don't you use some additional columns with a small widht like 10px? You could easily color-code like 10-20 of them, without using up a large amount of space.

---

Use a graph to visualize this information. This would be like an outsourced and pimped-up version of your color-coded columns.

You could make a graph for each employee, or one for all, or one for each area or a dynamic one, which changes with an employee-selection.

---

Create a PivotTable or a manual cross-table, which is like a text-version of the color-coded-columns.

           area1 | area2 | area 3 | ...
employee1    x                x
employee2            x

---

Depending on the number of employees, you could embed a combo-box for each of them. Using a macro, you could see for each of them, which areas are available. And as a kicker, your macro could even have some sort of algorithm to select the most important one on its own.

---

You could create a UserForm to switch between employees or look at all employees at once (this depends again on how many there are).

In a UserForm, you can again use combo-boxes, list boxes, color-codes or what ever, but your not restricted anymore to the worksheet-style.

---

And of course - the solution of Excellll has my support too.

Answer 4

I think “what is required?” may be about as relevant as “who is available?” (that is your table) so I’d suggest a repeat of the “by employee” table but “by job area”. The latter could be created by a formula such as =VLOOKUP(MATCH($B15,D$2:D$13,0),$A$2:$B$13,2,FALSE) in the example below in D15 and copied across and down. Certainly makes the ‘Not assigned’ (#N/A) stand out!

But by all means also keep the conditional formatting that shows an incomplete match that day.