Conditional formatting based on a range

[dbl_dbl]

I have a form that people fill in, and there are columns across the top. Each row is another module bolted on to the final product. However, each column does not necessarily need inputs, depending on the Part type selected.

In my example, we have 2 basic part types: Mechanical and Electrical.

If a Mechanical Part is selected, I would like to grey out the fields I choose are not required (I've made it easy in this sample - no electrical variable fields needed for mech parts).

The issue I am running into is that we add new mechanical parts occasionally to our list of options on the drop down, and I would like this logic to include the new parts (so it needs to be a range). However, I don't know if that is possible as I can only think of using the OR statement in the formatting which references individual cells.

Hoping someone has a better way to do this. I'd like to avoid macros since this sheet will be sent out externally.

In a perfect world, if a mechanical part was selected, I would actually not allow inputs in the electronic variables fields, but even just greying out is fine since the background formulas will ignore those inputs anyways.

[jomaor1]

Hi,

You would do like this:

• Select from B4 to C8
• Click Home > Conditional Formatting > New Rule...
• Select "Use a formula to determine which cells to format" (Last one)
• Type this in the formula bar: "=($A4="Electric 1")+($A4="Electric 2")" (The "plus sign" means "OR")
• Click Format... and in the Fill tab, click Pattern Style and choose the fourth one.
• After that, click OK!

Repeat like this:

• Select from D4 to E8
• Click Home > Conditional Formatting > New Rule...
• Select "Use a formula to determine which cells to format" (Last one)
• Type this in the formula bar: "=($A4="Mechanical 1")+($A4="Mechanical 2")" (The "plus sign" means "OR")
• Click Format... and in the Fill tab, click Pattern Style and choose the fourth one.
• After that, click OK!

Cheers!

[jomaor1]

Sorry,

I've failed to understand what you needed,
Follow updated file

Cheers!

[dbl_dbl]

Sorry,

I've failed to understand what you needed,
Follow updated file

Cheers!

Thanks John, I can't check the updated formula until I get home (work policy regarding downloading excel files) but once I do I will let you know and send you some rep if it works.