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.
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.
