Forum Discussion
Conditional Formatting based on cell contents and location
I'm really struggling here with some conditional formatting. Font colors in a range of cells need to change based on the cell above.
For Example: Cells L48-Z48 contain a number, Cells L49-Z49 contain a number. I want each number in Row 49 to be red if they are greater than the corresponding number in the Row 48 cell above.
L48=7 L49=7 - L49 font color should not change
M48=6 M49=7 - M49 font should be red
N48=7 N49=9 - N49 font should be red
O48=6 O49=5 - O49 font color should not change
It seems that I can only change the color of the range of cells based on a single target cell rather than the target cell being relative based on the formatted cell.
Is there a way to do this that I am just completely missing, or do I have to Conditionally format each cell separately? I feel like I'm missing something so simple, either using "format only cells that contain "greater than"... or Using an IF formula.
Select L49:Z49.
L49 should be the active cell in the selected range.
On the Home tab of the ribbon, click Conditional Formatting > New Rule...
Select 'Format only cells that contain'.
Leave the first drop down set to 'Cell Value'.
Select 'greater than' from the second drop down.
In the box next to it, enter the formula=L48
(Important: no $ sign before the column letter L!)
Click Format...
Activate the Fill tab.
Select red as fill color.
Click OK, then click OK again.
2 Replies
Select L49:Z49.
L49 should be the active cell in the selected range.
On the Home tab of the ribbon, click Conditional Formatting > New Rule...
Select 'Format only cells that contain'.
Leave the first drop down set to 'Cell Value'.
Select 'greater than' from the second drop down.
In the box next to it, enter the formula=L48
(Important: no $ sign before the column letter L!)
Click Format...
Activate the Fill tab.
Select red as fill color.
Click OK, then click OK again.Thanks, Hans. I didn't have a chance to delete my post but I already figured out that I needed the $ in front of the row and it would all work. I knew it was something so simple. I really appreciate your time.
