SOLVED

Conditional formatting a column or array if cell contains text

%3CLINGO-SUB%20id%3D%22lingo-sub-2380930%22%20slang%3D%22en-US%22%3EConditional%20formatting%20a%20column%20or%20array%20if%20cell%20contains%20text%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2380930%22%20slang%3D%22en-US%22%3ESo%20I%E2%80%99ve%20ran%20into%20a%20unique%20problem.%20I%E2%80%99ve%20been%20tasked%20with%20the%20creation%20of%20an%20automated%20schedule%20calendar.%20One%20of%20the%20items%20desired%20is%20to%20format%20all%20cells%20in%20a%20column%20if%20the%20cell%20in%20the%20day%20row%20contains%20%E2%80%9CSun%E2%80%9D%20or%20%E2%80%9CSat%E2%80%9D.%3CBR%20%2F%3E%3CBR%20%2F%3EI%20know%20of%20methods%20to%20conditional%20format%20rows%20based%20off%20of%20a%20cell%20using%20the%20%3D%24X%23%20route.%20Is%20there%20a%20way%20to%20do%20the%20inverse%3F%20So%20if%20cell%20in%20row%20%23%20contains%20%E2%80%9CSun%E2%80%9D%20or%20%E2%80%9CSat%E2%80%9D%20then%20format%20border%20in%20that%20column%20or%20cells%20*1%3A*300.%20Apply%20to%20A1%3AZ300%3F%3CBR%20%2F%3E%3CBR%20%2F%3EThanks%20for%20the%20help.%20This%20one%20has%20me%20stumped.%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2380930%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2380992%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20formatting%20a%20column%20or%20array%20if%20cell%20contains%20text%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2380992%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1061754%22%20target%3D%22_blank%22%3E%40Shoeball%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ELet's%20say%20the%20day%20row%20is%20row%20%233.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ESelect%20A1%3AZ300.%20A1%20should%20be%20the%20active%20cell%20in%20the%20selection.%3C%2FP%3E%0A%3CP%3EOn%20the%20Home%20tab%20of%20the%20ribbon%2C%20click%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%26nbsp%3B%20%3DOR(%24A3%3D%22Sat%22%2C%24A3%3D%22Sun%22)%3C%2FP%3E%0A%3CP%3EThere%20is%20no%20%24%20before%20the%20column%20letter%20A%20because%20the%20column%20has%20to%20be%20adjusted%20dynamically.%3C%2FP%3E%0A%3CP%3EThere%20is%20a%20%24%20before%20the%20row%20number%203%20because%20the%20formatting%20must%20depend%20on%20the%20cell%20in%20row%203%20for%20ALL%20cells%20in%20the%20column.%3C%2FP%3E%0A%3CP%3EClick%20Format...%3C%2FP%3E%0A%3CP%3ESelect%20the%20Fill%20tab.%3C%2FP%3E%0A%3CP%3ESelect%20a%20highlight%20color.%3C%2FP%3E%0A%3CP%3EClick%20OK%2C%20then%20click%20OK%20again.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2381065%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20formatting%20a%20column%20or%20array%20if%20cell%20contains%20text%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2381065%22%20slang%3D%22en-US%22%3EThank%20you%20very%20much.%20That%20worked%20like%20a%20charm!%3C%2FLINGO-BODY%3E
New Contributor
So I’ve ran into a unique problem. I’ve been tasked with the creation of an automated schedule calendar. One of the items desired is to format all cells in a column if the cell in the day row contains “Sun” or “Sat”.

I know of methods to conditional format rows based off of a cell using the =$X# route. Is there a way to do the inverse? So if cell in row # contains “Sun” or “Sat” then format border in that column or cells *1:*300. Apply to A1:Z300?

Thanks for the help. This one has me stumped.
2 Replies
best response confirmed by Shoeball (New Contributor)
Solution

@Shoeball 

Let's say the day row is row #3.

 

Select A1:Z300. A1 should be the active cell in the selection.

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

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

Enter the formula  =OR($A3="Sat",$A3="Sun")

There is no $ before the column letter A because the column has to be adjusted dynamically.

There is a $ before the row number 3 because the formatting must depend on the cell in row 3 for ALL cells in the column.

Click Format...

Select the Fill tab.

Select a highlight color.

Click OK, then click OK again.

Thank you very much. That worked like a charm!