CODEWITHSUNDEEP
excelcolorscells
archaeologist
archaeologist

Reputation: 11

Change cell colour according to value in adjacent column

I'm sure this is very easy, but I can't make conditional formatting work for me. What I am trying to do (example attached), is have one column for date, one column for an action to be taken and one column to assert whether the action is done or not (y /N). This worksheet will be expanding every day so needs to be future-proofed.

I am trying to get the cells in column b to either display Red when an 'n' is displayed in the adjacent cell in Column C, or change to Green when a 'y' is entered in column c.

I can get one cell to do it, but when I apply the formatting to the whole column, it changes ALL the cells in column 'B' to green when I type a 'Y' see example . I suppose what I need is an 'if' statement, or a way of dragging a formula down the column B - but I can't work it out.

Any help gratefully appreciated!

Upvotes: 1

Views: 2777

Answers (1)

alseether
alseether

Reputation: 1993

Try this, select the cell where you want the background color and click "Conditional formatting" then add a "New rule" and select "Use a formula to determine which cells to format" option. In the formula field write =EXACT(CELL;"y"), where CELL is the cell you have your y/n value. In the format, set the background green or the color you want.

This way you can add another rule to check if content is n an set bg red.

With autocomplete of excel just drag down and every cell should updates their conditions to check their adjacent cells.

It should look like:

enter image description here

Upvotes: 2

Related Questions