Forum Discussion
conditional formatting based on content of another cell
- Apr 13, 2021
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.
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
- HansVogelaarJul 10, 2024MVP
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.- FloriszJul 15, 2024Brass Contributor
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"- HansVogelaarJul 15, 2024MVP
If you look closely, you'll see that the formula refers to C2 instead of to C3. You should edit the rule and change the formula to =C3<>"Yes"