Apr 13 2021 07:16 AM - last edited on Nov 09 2023 11:09 AM by
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!
Apr 13 2021 08:15 AM
SolutionSelect 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.
Apr 13 2021 08:25 AM
Awesome! That works perfectly! Thank you Hans Vogelaar! Hope you are keeping well and safe.
Dec 06 2023 12:19 AM
Dec 06 2023 03:19 AM
Are you using the desktop version of Excel for Windows? Or Mac? Or are you using Excel Online (in the browser)?
Mar 04 2024 12:54 PM
Mar 04 2024 01:28 PM
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".
Mar 04 2024 03:09 PM
@Hans Vogelaar That worked. Thanks.
Mar 07 2024 05:53 AM
@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.
Mar 07 2024 07:01 AM
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
Mar 14 2024 01:40 PM
@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!
Mar 14 2024 03:16 PM
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.
Mar 15 2024 06:34 AM
@Hans Vogelaar Thanks but the cells below C2 are still referencing A2, instead of C3 referencing A3, C4 with A4. Any further advise?
Mar 15 2024 07:02 AM
Mar 15 2024 08:53 AM
@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.
Mar 15 2024 09:40 AM
Mar 15 2024 10:07 AM
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.
Mar 15 2024 10:27 AM
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.
Mar 15 2024 01:14 PM
I get "Unauthorized". Can you give read-only access to the file?
Apr 13 2021 08:15 AM
SolutionSelect 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.