Affect multiple columns with one conditional formatting rule?

I have four columns, A, B, C, and D. If the entry in column A is "NO", the I want columns B, C, and D (within the same row) to be blacked out.

The only way I can see being able to do this is to put a rule in for each column. Is it (would it) be possible to check multiple columns in the selection field when creating the rule (hold control, or something)?

I tried to select multiple when making the rule and couldn't figure out how to do it.

Thanks.

Find more posts tagged with

Conditional Formatting

Comments

Travis

Jamison, you would need to create a new rule for each column but you can quickly clone a rule by right clicking on the rule and selecting Clone Rule.

Tim Meeks

Jamison, I just did this for 3 columns, and I wanted the other columns to be Red if one was updated correctly but the other 2 weren't. It took 6 rules but it can be done. As Travis said you can clone the rules to make it go faster. Plus now that the Drag/Drop reorder functionality is implemented to move the rules, it's much faster.

Also, you could do this with an excel formula. You would have to add another column to check if a NO exists in column A and then set this New column to display some value. If the value is set, then have one conditional formula that looks at the NEW column and if the value is found, blacks out columns B, C, D.

Not as clean but would require only one conditional formula. I like using 3 conditional formulas.

Tim

Jamison

Thanks - I understand that cloning is a nice way to save some time, but If I have 20 columns that are dependent of the state of one column, being able to select muliple columns in the rule making process would save a considerable amount of time.

Is this something that could be considered in the future? I am no developer, but could it even be automated to create mulitple rules as a result of the one rule creating process (by marking all columns that we wish to be affected)?

Jamison

@Tim In your example, you still have one formatting rule blocking out 3 columns. How is that possible?

Jamison

Just happened to search for this again to see if thier had been any updates. Doesn't look like it.

Lynnette Batt

i'd second this feature. I have 20 columns that all have similar inputs (but for different year) and I'd like to code it such that an "x" marked in any of the cells within the 20 columns gets a purple highlight. No easy way to do this. I have to clone rules and then change them one by one, and then I have 20 rules. Messy. Would be great if I could CTRL select multiple columns for the rule.

Travis

Everyones votes are added for this request. Thanks for the feedback!

elijin

Include my vote too.

Tracey Siepser

Include my vote too!

One_SB

Another vote. This would help as 104 rules would become 4 rules,

Tim Meeks

@Jamison, I got an alert on this post and realzied I never answered your question back in Aug 2015. Sorry.

Here is a screen shot using 6 rules to peform formatting on 3 columns. Basically, if a Project is in Complete Status, then the % complete must be 100% and the Date completed must not be blank. You know how us IT people like redundant data input.

Conditional Formatting.JPG

Shaine Greenwood

Hi All—I've got your votes down for this on our enhancement request list for further consideration.

I appreciate your input!

Lori Manna

+vote from me. Thank you

Nick Riek

Add another vote for this feature. I waste so much time cloning rules across sheets to make updates. We use a lot of conditional formats and I'd rather have a handful of rules compared to one per column. It's cubersome and doesn't need to be.

Shaine Greenwood

Lori & Nick—

Got your votes down for this feature on our enhancement request list as well.

Thanks for the feedback!