Formatting with Multiple/Varying Conditions

bowelch4

New Member
Joined
Nov 14, 2019
Messages
3
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
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
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?
 
Upvote 0

Forum statistics

Threads
1,212,932
Messages
6,110,748
Members
448,295
Latest member
Uzair Tahir Khan

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top