Formatting with Multiple/Varying Conditions

[bowelch4]

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.

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[TD="align: center"]H[/TD]
[TD="align: center"]I[/TD]
[TD="align: center"]J[/TD]
[TD="align: center"]K[/TD]
[TD="align: center"]L[/TD]
[TD="align: center"]M[/TD]
[TD="align: center"]N[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Sitting[/TD]
[TD]Occasional[/TD]
[TD]O[/TD]
[TD]F[/TD]
[TD]O[/TD]
[TD]O[/TD]
[TD]F[/TD]
[TD]F[/TD]
[TD]O[/TD]
[TD]L[/TD]
[TD]L[/TD]
[TD]O[/TD]
[TD]F[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Working from Ladders[/TD]
[TD]No[/TD]
[TD]L[/TD]
[TD]O[/TD]
[TD]O[/TD]
[TD]O[/TD]
[TD]L[/TD]
[TD]O[/TD]
[TD]F[/TD]
[TD]F[/TD]
[TD]O[/TD]
[TD]L[/TD]
[TD]O[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Kneeling[/TD]
[TD]Frequent[/TD]
[TD]F[/TD]
[TD]O[/TD]
[TD]O[/TD]
[TD]O[/TD]
[TD]L[/TD]
[TD]O[/TD]
[TD]F[/TD]
[TD]F[/TD]
[TD]O[/TD]
[TD]O[/TD]
[TD]L[/TD]
[TD]O[/TD]
[/TR]
</tbody>[/TABLE]

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.


[TABLE="class: grid, align: center"]
<tbody>[TR]
[TD]1[/TD]
[TD]Sitting[/TD]
[TD]Occasional[/TD]
[TD]O[/TD]
[TD]F[/TD]
[TD]O[/TD]
[TD]O[/TD]
[TD]F[/TD]
[TD]F[/TD]
[TD]O[/TD]
[TD]L[/TD]
[TD]L[/TD]
[TD]O[/TD]
[TD]F[/TD]
[TD]F[/TD]
[/TR]
</tbody>[/TABLE]


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

 

[Fluff]

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?