Forum Discussion
conditional formatting based on content of another cell
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.
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.
I am having trouble with the highlight cell rules. I want to create a rule that turns a B cell green if it has the same value as the D cell in it's same row. I need this to apply to both columns though. Everytime I try I can only get the B column to format itself to just one D cell, not the D cell in the same row as a given B cell. In other words it won't let me select a range.
Can you help?
Assuming data is in B2:B7 and D2:D7 apply the rule to combined range as
Formula shall be for the first row in the ranges.
How do I format the column next to another if I want it change colour only if the number in the cell to the left is equal to or higher than a 3? I managed to use your previous formula if any number is entered but need one for if a 3 or more is entered and simply can't figure it out!!
Thanks
Let's say you want to conditionally format D2:D100.
Select this range. D2 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=C2>=3
(C2 is the cell to the left of the active cell)
Click Format...
Activate the Fill tab.
Select a fill color.
Click OK, then click OK again.HansVogelaar , first of all thank you for all the help to this challenge.
I think 1 screenshot will help more than a 1000 words.
Like this one. Why would it highlight Red, while value is really NOT <>"Yes"
HansVogelaar Hello. Is it possible to highlight a range of cells in a single row based on the value of another single cell in that same row, and for this process to be replicated to the rows below? In my case I need the following to apply. If F2>2.5, highlight A2:D2 and if F3>2.5, highlight A3:D3 and so on.
Select A2:D100 or however far down you want. The active cell in the selection should be in row 2.
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=$F2>2.5
Click Format...
Activate the Fill tab.
Select a highlight color.
Click OK, then click OK again.- This works perfectly! Thank you very much.
Hi. I'm wondering if you can help me, too.
I am tracking weights in a spreadsheet and want to use conditional formatting to turn a cell either green or light red based on the weight from the previous day. Green if the weight has increased; red if it has decreased. my cell ranges are B2 through BF9 to account for now until July 13th.
Thank you!
Select C2:BF9 (we'll skip column B since there is no previous value). 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=AND(B2<>"", C2<>"", C2>B2)
Click Format...
Activate the Fill tab.
Select green as fill color.
Click OK, then click OK again.Repeat these steps, but with the formula
=AND(B2<>"", C2<>"", C2<B2)
and red as fill color.
HansVogelaar Sorry, I'm hoping you may be able to help me too?
I'm trying to format my 'to do' list for an upcoming event. Column 'B' has the task, column 'C' has the due date, and column 'E' has the status of the task ('in progress' or 'complete'). I have conditional formatting set up so that the due date (column 'C') turns blue if it is due in the next 2 weeks, yellow if it due in the next week and red if the due date has passed. I then also have conditional formatting set up so that the status (column 'E') is yellow if it is 'in progress' and green if it is 'complete'.
What I would now like to do is set up a formula so that if column 'E' is set to 'complete' the conditional formatting changes for column 'C' (due date) to grey or some colour. At the moment tasks that I have previously completed and are passed their due date are still showing as red and it makes it harder to see what still needs doing.
Hope that makes sense and that you have a solution. Thanks!
Hi,
I am also trying to apply conditional formatting to change the colour of a cell based on the value of a different cell but without replacing the value of the cell.
I already have other conditions set up for the other cells whereby when I enter a number value into C column, the value in the corresponding E column cell automatically changes to Low, Normal, or High, and also changes to blue, green, or red respectively.
I'm having issues with the final step, ideally. When a cell in column E changes to a colour, I want the same cell in column C to change to the same colour but without replacing the value in the C cell.
I have managed to find a way to do it for each individual cell but can't get it to work for the entire column with 1 formula.
For example I selected C4 as active cell and applied the condition formula =E4="Low" and selected fill as blue. Now when E4 reads Low C4 turns blue. But I'm struggling to work out how to apply this to both columns without having to do all cells individually. I thought I sussed it but then my entire C column turned blue based off the value of E4 but I want each cell to respond individually to its adjacent cell.
So C5 changes only based on E5 and C26 changes only to E26 etc.
I know I need to repeat 3 separate formulas for each colour but struggling to get the first one sorted
Let's say you want to apply this to C4:C200.
Select this range. C4 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=E4="Low"
Note that there is no $ sign in the cell reference. This means that the reference is relative, i.e. Excel will automatically adjust it to E5 for C5, to E6 for C6 etc.
Click Format...
Activate the Fill tab.
Select blue as highlight color.
Click OK, then click OK again.Repeat for the other two rules.
HansVogelaar 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.
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.
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=111409104718401052391&rtpof=true&sd=true
HansVogelaar 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.
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
- 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.
Are you using the desktop version of Excel for Windows? Or Mac? Or are you using Excel Online (in the browser)?
- 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?
Awesome! That works perfectly! Thank you Hans Vogelaar! Hope you are keeping well and safe.
