Conditional formatting a column or array if cell contains text

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)


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!