Home

Conditional formatting a range of cells in a table based on text in a drop down list

%3CLINGO-SUB%20id%3D%22lingo-sub-1054768%22%20slang%3D%22en-US%22%3EConditional%20formatting%20a%20range%20of%20cells%20in%20a%20table%20based%20on%20text%20in%20a%20drop%20down%20list%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1054768%22%20slang%3D%22en-US%22%3E%3CP%3EHello%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20issue%20is%20for%20Excel%20in%20Office%20365.%26nbsp%3B%20%26nbsp%3BPlease%20view%20the%20attached%20screenshots%20as%20part%20of%20the%20explanation.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESS%20Excel_01%20shows%20a%20cell%20in%20a%20dropdown%20list%20used%20in%20a%20formula%20for%20formatting%202%20ranges%20of%20cells%20in%20a%20spreadsheet.%26nbsp%3B%20As%20written%20the%20rule%20only%20formats%20the%20desired%20cells%20in%20only%20one%20row%20(7).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMy%20understanding%20is%20in%20order%20for%20the%20rule%20to%20be%20propagated%20to%20the%20other%20rows%20in%20the%20table%20the%20%22%24%22%20must%20be%20removed%20in%20the%20rule's%20test%20in%20the%20formula.%26nbsp%3B%20When%20this%20is%20done%20(reference%20SS%20Excel_02)%2C%20only%20one%20of%20the%20cells%20in%20the%20range%20that%20was%20previously%20formatted%20correctly%20gets%20formatted.%26nbsp%3B%20Although%20the%20formatting%20is%20turn%20on%20and%20off%20correctly%20the%20cells%20that%20get%20formatted%20are%20incorrect.%26nbsp%3B%20%26nbsp%3BAdditionally%2C%20the%20rule%20is%20still%20not%20propagated%20to%20the%20remaining%20rows%20in%20the%20table.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20was%20told%20by%20MS%20level%201%20support%20that%20additional%20rules%20are%20needed%20to%20get%20this%20to%20work%20correctly%2C%26nbsp%3B%20but%20he%20could%20not%20offer%20any%20help.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20suggestions%20would%20be%20appreciated.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1054768%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EConditional_Formatting%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EDropDown_Lists%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Highlighted
CheechGe
Occasional Visitor

Hello:

 

This issue is for Excel in Office 365.   Please view the attached screenshots as part of the explanation.

 

SS Excel_01 shows a cell in a dropdown list used in a formula for formatting 2 ranges of cells in a spreadsheet.  As written the rule only formats the desired cells in only one row (7).

 

My understanding is in order for the rule to be propagated to the other rows in the table the "$" must be removed in the rule's test in the formula.  When this is done (reference SS Excel_02), only one of the cells in the range that was previously formatted correctly gets formatted.  Although the formatting is turn on and off correctly the cells that get formatted are incorrect.   Additionally, the rule is still not propagated to the remaining rows in the table.

 

I was told by MS level 1 support that additional rules are needed to get this to work correctly,  but he could not offer any help.

 

Any suggestions would be appreciated.

 

Thank you.