Formatting with Multiple/Varying Conditions

bowelch4

New Member
Joined
Nov 14, 2019
Messages
1
Hi Everyone - Need some help in finding a way to have a cell change color based off a drop down, and each time that drop down changes, it will potentially change the color of the cell, based off of the current letter in the cell. See below an example of a few rows. My book has 54 rows, but if we are able to figure it out on 1 row I think I can do it to the rest.

ABCDEFGHIJKLMN
1SittingOccasionalOFOOFFOLLOFF
2Working from LaddersNoLOOOLOFFOLOF
3KneelingFrequentFOOOLOFFOOLO

<tbody>
</tbody>

Key: Limited = 0-1 Hours, Occasional = 1-3 Hours, Frequent = 3-6 Hours

OK - So column B has a drop down list added to it where I can choose from a number of different options (Occasional, Frequent, Limited, Yes, No, Moderate). Column B reflects someone ability to perform a physical demand of a job. So for instance, this employee can only sit occasionally (1-3 hours), No working from ladders, and kneel frequently (3-6 hours). Columns C-N are different jobs and the requirements of those jobs. So column C has a requirement of Occasional (1-3 hours) sitting, where this employee would meet the demands of job in column C for sitting. However the job in column D has a requirement to be sitting frequently (3-6 hours), where the employee WOULD NOT meet the physical requirements of the job. You can see the other columns and how they would impact the ability. Columns C-N will always remain the same. Column A will also remain the same. I need to find a way to have column B change, and each time, color the cells in columns C-N, based off of the letter in that cell.


1SittingOccasionalOFOOFFOLLOFF

<tbody>
</tbody>


For instance, For row 1 as it looks above, I would need all of the O and L cells to be green because this employee meets the physical demands of the cells with O and L (The can sit occasionally = 1-3 hours, so they can meet the L (limited) = 0-1 hours). However, they do not meet the requirements for any cell with F (Frequently) = 3-6 hours. I would want the cells with F to be red. And since the "Occasional" cell is a drop down, I may need to change that to Limited, which would then require the cells with L to be green, but O and F to be red. So each time the drop down choice changes, I need the colors of the cells next to it to reflect red (cannot perform job) or green (can perform job).

Is this possible? I am happy to expand if you think that would help.

Thanks!

Bo
 

Some videos you may like

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
35,977
Office Version
365
Platform
Windows
Hi and welcome to MrExcel.
Select C2:N54 > conditional formatting > New rule > use a formula
=OR(AND(LEFT($B2)="O",C2="L"),AND(LEFT($B2)=C2),AND(LEFT($B2)="F",OR(C2="O",C2="L")))
Select format for green > ok

What should happen if B is Yes, No, or moderate?
 

Watch MrExcel Video

Forum statistics

Threads
1,090,191
Messages
5,412,972
Members
403,459
Latest member
acharnp

This Week's Hot Topics

Top