SOLVED

Conditinal formatting in a table, multiple rows

%3CLINGO-SUB%20id%3D%22lingo-sub-3089979%22%20slang%3D%22en-US%22%3EConditinal%20formatting%20in%20a%20table%2C%20multiple%20rows%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3089979%22%20slang%3D%22en-US%22%3E%3CP%3EHi.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20such%20a%20table%2C%20how%20do%20I%20highlight%20with%20colour%20both%20instances%20of%20%22Company%20A%22%20(B2%20and%20B3)%2C%20when%20just%20putting%20an%20%22x%22%20in%20C2%3F%20Same%20for%20Company%20B%20and%20so%20on.%3C%2FP%3E%3CP%3EThe%20file%20contains%20many%20more%20columns%20with%20different%20data%20in%2C%20and%20someone%20is%20to%20go%20through%20and%20mark%20the%20%3CU%3E%3CSTRONG%3Eone%3C%2FSTRONG%3E%20%3C%2FU%3Ecorrect%20column%20for%20each%20company.%20I%20then%20want%20to%20highlight%20ALL%20rows%20for%20that%20company%20as%20the%20review%20of%20it%20is%20done.%20I%20can%20create%20additional%20helper%20columns%20if%20needed.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CTABLE%20border%3D%221%22%20width%3D%22100.00000000000001%25%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20height%3D%2225px%22%3E%3CSTRONG%3E%26nbsp%3B%3C%2FSTRONG%3E%3C%2FTD%3E%3CTD%20height%3D%2225px%22%3E%3CSTRONG%3EA%3C%2FSTRONG%3E%3C%2FTD%3E%3CTD%20height%3D%2225px%22%3E%3CSTRONG%3EB%3C%2FSTRONG%3E%3C%2FTD%3E%3CTD%20height%3D%2225px%22%3E%3CSTRONG%3EC%3C%2FSTRONG%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%222.3616734143049953%25%22%20height%3D%2230px%22%3E%3CSTRONG%3E1%3C%2FSTRONG%3E%3C%2FTD%3E%3CTD%20width%3D%2230.97165991902834%25%22%20height%3D%2230px%22%3E%3CSTRONG%3EID%3C%2FSTRONG%3E%3C%2FTD%3E%3CTD%20width%3D%2233.333333333333336%25%22%20height%3D%2230px%22%3E%3CSTRONG%3EName%3C%2FSTRONG%3E%3C%2FTD%3E%3CTD%20width%3D%2233.333333333333336%25%22%20height%3D%2230px%22%3E%3CSTRONG%3EKeep%20row%20%3D%20x%3C%2FSTRONG%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%222.3616734143049953%25%22%20height%3D%2230px%22%3E%3CSTRONG%3E2%3C%2FSTRONG%3E%3C%2FTD%3E%3CTD%20width%3D%2230.97165991902834%25%22%20height%3D%2230px%22%3E111%3C%2FTD%3E%3CTD%20width%3D%2233.333333333333336%25%22%20height%3D%2230px%22%3ECompany%20A%3C%2FTD%3E%3CTD%20width%3D%2233.333333333333336%25%22%20height%3D%2230px%22%3Ex%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%222.3616734143049953%25%22%20height%3D%2230px%22%3E%3CSTRONG%3E3%3C%2FSTRONG%3E%3C%2FTD%3E%3CTD%20width%3D%2230.97165991902834%25%22%20height%3D%2230px%22%3E111%3C%2FTD%3E%3CTD%20width%3D%2233.333333333333336%25%22%20height%3D%2230px%22%3ECompany%20A%3C%2FTD%3E%3CTD%20width%3D%2233.333333333333336%25%22%20height%3D%2230px%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%222.3616734143049953%25%22%20height%3D%2230px%22%3E%3CSTRONG%3E4%3C%2FSTRONG%3E%3C%2FTD%3E%3CTD%20width%3D%2230.97165991902834%25%22%20height%3D%2230px%22%3E222%3C%2FTD%3E%3CTD%20width%3D%2233.333333333333336%25%22%20height%3D%2230px%22%3ECompany%20B%3C%2FTD%3E%3CTD%20width%3D%2233.333333333333336%25%22%20height%3D%2230px%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%222.3616734143049953%25%22%20height%3D%2230px%22%3E%3CSTRONG%3E5%3C%2FSTRONG%3E%3C%2FTD%3E%3CTD%20width%3D%2230.97165991902834%25%22%20height%3D%2230px%22%3E222%3C%2FTD%3E%3CTD%20width%3D%2233.333333333333336%25%22%20height%3D%2230px%22%3ECompany%20B%3C%2FTD%3E%3CTD%20width%3D%2233.333333333333336%25%22%20height%3D%2230px%22%3Ex%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%222.3616734143049953%25%22%20height%3D%2230px%22%3E%3CSTRONG%3E6%3C%2FSTRONG%3E%3C%2FTD%3E%3CTD%20width%3D%2230.97165991902834%25%22%20height%3D%2230px%22%3E222%3C%2FTD%3E%3CTD%20width%3D%2233.333333333333336%25%22%20height%3D%2230px%22%3ECompany%20B%3C%2FTD%3E%3CTD%20width%3D%2233.333333333333336%25%22%20height%3D%2230px%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3089979%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3090007%22%20slang%3D%22en-US%22%3ERe%3A%20Conditinal%20formatting%20in%20a%20table%2C%20multiple%20rows%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3090007%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1291705%22%20target%3D%22_blank%22%3E%40roboed%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThe%20Name%20column%2C%20starting%20in%20row%202.%3C%2FP%3E%0A%3CP%3EB2%20should%20be%20the%20active%20cell%20in%20the%20selection.%3C%2FP%3E%0A%3CP%3EOn%20the%20Home%20tab%20of%20the%20ribbon%2C%20select%20Conditional%20Formatting%20%26gt%3B%20New%20Rule...%3C%2FP%3E%0A%3CP%3ESelect%20'Use%20a%20formula%20to%20determine%20which%20cells%20to%20format'.%3C%2FP%3E%0A%3CP%3EEnter%20the%20formula%20%3DCOUNTIFS(%24B%242%3A%24B%24100%2C%24B2%2C%24C%242%3A%24C%24100%2C%22x%22)%3C%2FP%3E%0A%3CP%3EAdjust%20the%20ranges%20as%20needed.%3C%2FP%3E%0A%3CP%3EClick%20Format...%3C%2FP%3E%0A%3CP%3ESpecify%20the%20desired%20formatting%2C%20for%20example%20in%20the%20Fill%20tab.%3C%2FP%3E%0A%3CP%3EClick%20OK%2C%20then%20click%20OK%20again.%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Hi.

 

In such a table, how do I highlight with colour both instances of "Company A" (B2 and B3), when just putting an "x" in C2? Same for Company B and so on.

The file contains many more columns with different data in, and someone is to go through and mark the one correct column for each company. I then want to highlight ALL rows for that company as the review of it is done. I can create additional helper columns if needed.

 

 ABC
1IDNameKeep row = x
2111Company Ax
3111Company A 
4222Company B 
5222Company Bx
6222Company B 
2 Replies
best response confirmed by roboed (New Contributor)
Solution

@roboed 

The Name column, starting in row 2.

B2 should be the active cell in the selection.

On the Home tab of the ribbon, select Conditional Formatting > New Rule...

Select 'Use a formula to determine which cells to format'.

Enter the formula =COUNTIFS($B$2:$B$100,$B2,$C$2:$C$100,"x")

Adjust the ranges as needed.

Click Format...

Specify the desired formatting, for example in the Fill tab.

Click OK, then click OK again.

Thanks, worked great. I was thinking too advanced