conditional formatting based on content of another cell

[alvarezb]

Hi all. I cannot figure out how to colour one cell based on the content of another. 

I have successfully formatted one cell to be red if its empty and orange if it is non empty. However, I don't know how to then change it green if the cell next to it is non-empty. Specifically, column A has an action due date (red if not date entered, orange if a date is entered). Column B is the actual completion date. So if Column B has a date in it, I wish Column A to turn green. Any ideas? Thank you!

[#2 · Accepted solution]

@alvarezb 

Select the cells that you want to format conditionally. If more than one, the top left cell should be the active cell in the selection. Note the address of this cell.

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

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

Enter a formula that evaluates to TRUE (or a non-zero number) if the rule should be applied, and to FALSE (or 0) otherwise.

In your example, let's say A2 is the active cell in the selection.

Use the following formula:

=B2<>""

Click Format...

Activate the Fill tab.

Specify the desired color (green in your example).

Click OK, then click OK again.

[#3]

@Hans Vogelaar 

Awesome! That works perfectly! Thank you Hans Vogelaar! Hope you are keeping well and safe.

[#4]

Any idea how to make it work with Microsoft 365? There is no 'Use a formula to determine which cells to format' option, so I just selected the Rule Type 'Highlight cells with', but that didn't work.

[#5]

@nicoarcenas 

Are you using the desktop version of Excel for Windows? Or Mac? Or are you using Excel Online (in the browser)?

[#6]

Excel on Mac version 16.82. Trying to have column C highlight red if column P contains TRUE. Formula '=P2="TRUE"'. But when applied to all rows, the is still '=P2="TRUE"' rather than '=P<row>="TRUE"'. How do I get the rule to change based on the row it applies to?

[#7]

@DHurley

Select the range in column C that you want to format. C2 should be the top cell in the selection, and also the active cell.

Use =P2 as formula instead of =P2="TRUE".

[#8]

@Hans Vogelaar That worked. Thanks.

[#9]

@Hans Vogelaar  Hello. I want to apply conditional formatting to cells in one column based on dates in another column. In this case the date format is 2024-01-01. I have entered the formula =ad28=2024-01-01 to a cell where this is True and nothing happens. The formula looks the same to me as your example =B2<>" so why is it not working? Thank you.

[#10]

@David_Cadrin18 

In your formula, Excel treats 2024-01-01 as a subtraction resulting in 2022.

For a date, use the DATE function: DATE(2024, 1, 1):

=AD28=DATE(2024, 1, 1)

Or enter the date 2024-01-01 in a cell, for example in Z1. You can then use

=AD28=$Z$1

[#11]

@Hans Vogelaar I am trying to change the font colour of cell C2 if A2="Target to Liq" and for this to apply down the column i.e., change the font colour of cell C6 if A6="Target to Liq". Could you advise please? Many thanks!

[#12]

@MeiOoi 

Select C2:C100 or however far down you want. C2 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

=$A2="Target to Liq"

Click Format...
Activate the Fill tab.
Select a highlight color.
Click OK, then click OK again.

[#13]

@Hans Vogelaar Thanks but the cells below C2 are still referencing A2, instead of C3 referencing A3, C4 with A4. Any further advise?

[#14]

@MeiOoi 

Are you sure that you used=$A2="Target to Liq" ?

It should NOT be =$A$2="Target to Liq"

[#15]

@Hans Vogelaar I have been reading this series of Q&A and trying to follow. My question is regarding keeping the color in a cell as future data changes. Trying to track staff production. I have it all set with colors and conditional formatting. However, as their experience levels goes up so should their production levels. As I change their experience level (3,2,1), the previous cells do not retain the same colors previously assigned. Example: if you are experience level 3, you should produce 10 items weekly, red if 0-7, yellow if 8-9, green if 10 or above. Then as the experience level changes to 2, production should increase to 15 weekly, color are the same but increase to 0-12, 13-14 and 15 or higher. Yet, when I enter data on the first cell after the exp. level change, all colors turn and remain the same as the last color. I cannot figure out how to keep the colors as they were to show historical data/production via colors and not just numbers. Thanks for any assistance. 

[#16]

Hi. I may have left out the first = sign. It works now =A2="Target to Liq". Thank you!

[#17]

@rita64 

You cannot do that with conditional formatting (unless you store their experience level with each week). It would require VBA code.

If you'd like help with that, please provide a sample workbook without sensitive data, as an attachment or via Google Drive, Onedrive, Dropbox or similar.

[#18]

@Hans Vogelaar 

I would prefer the experience level to be one column with the number changing and a date of the change. However, in attempting to figure this out, I have added extra columns and was trying to make it such that the formula could be driven by a date change. I'm at a loss and this is Day 2 of working on it all day. Any assistance would be greatly appreciated. It is the tab labeled Team Data. 

https://docs.google.com/spreadsheets/d/1MqOiqMggGz1q5wQ0OEig0aXiufN6WHhs/edit?usp=sharing&ouid=11140...

[#19]

@rita64 

I get "Unauthorized". Can you give read-only access to the file?

[#20]

https://docs.google.com/spreadsheets/d/1MqOiqMggGz1q5wQ0OEig0aXiufN6WHhs/edit?usp=sharing&ouid=11140...