How to change the background colour of a cell based on content of another cell

Jon2020 

OK, here's how to do it:

Let's say that the Day cells are D2:D50, and you highlight a cell if it contains "Sun".

And let's say the Date cells are H2:H50.

Select H2:H50. H2 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

=ISNUMBER(SEARCH("Sun",D2))

In this formula, "Sun" is the specific text in the conditional formatting rule for the Day cells, and D2 is the Day cell in the same row as the active cell.

Click Format...

Activate the Fill tab.

Select a colour.

Click OK, then click OK again.