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

I am trying to format a cell based on another cell's content, the context is to link when a certain day is entered into a cell then a different cell will change colour, any suggestions?

excel

HansVogelaar
Apr 21, 2022
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.

Jon2020

Copper Contributor

Apr 20, 2022

I am trying to format the 'Date' cell in my spreadsheet to change colour dependent upon what is present in my 'Day' cell i.e. If it says Sunday then cell background colour changes to blue the some as the Sunday cell.

HansVogelaar

MVP

Apr 20, 2022

Jon2020

How is the 'Day' cell colored? Using conditional formatting or manually?

Jon2020
Copper Contributor
Apr 21, 2022
Conditional Formatting
- HansVogelaar
  MVP
  Apr 21, 2022
  Jon2020
  
  Thanks. Since there is a 'rule' for the coloring of the Day cells, you can use a modified form of that rule for the Date cells.
  
  It would be helpful to know what conditional formatting rule you're using for the Day cells.
  - Jon2020
    Copper Contributor
    Apr 21, 2022
    Formatting only cells that contain - Specific Text

Forum Discussion

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

Resources