Forum Discussion
Conditional formatting columns based on sums of rows
Hello! I'm wondering if it's possible to to create a conditional formatting rule that will apply to each cell in a column based on the various sums of only their corresponding rows.
In my case, I would like to be able to make the cell red if the value of my "PU" columns is greater than the sum of its corresponding "HSE" and "BLK" values. For example, cell W17 is greater than the sum of cells U17 and V17.
I know I could create a new rule for each group of 3 cells, but I would prefer not to do that if it can be avoided.
Thank you!
Select Z3:Z20.
Hold down Ctrl and select W3:W20, adding it to the selection.
Still holding down Ctrl, also select T3:T20 and finally Q3:Q20.
Q3 should be the active cell in the selection
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=SUM(O3:P3)
Click Format...
Activate the Fill tab.
Select a highlight color.
Click OK, then click OK again.
2 Replies
Select Z3:Z20.
Hold down Ctrl and select W3:W20, adding it to the selection.
Still holding down Ctrl, also select T3:T20 and finally Q3:Q20.
Q3 should be the active cell in the selection
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=SUM(O3:P3)
Click Format...
Activate the Fill tab.
Select a highlight color.
Click OK, then click OK again.- Thank you!
That was simpler than I was expecting. I was getting caught up on the auto $locked cell reference in formatting. I also didn't know that the "active cell" selection would make a difference. Much appreciated!
