Conditional Formatting help....formula based...

[aqalna]

Hello,

I need help on conditional formatting. In coloumn "P" in my excel sheet I want to apply conditional formating where if that sum total does not exceed or is less than the total of columns "A + B + C +D". I would like the colour to highlight when it does exceed or less than those amounts in red....can anyone help me. It is a newbie question and appreciate if someone can help me so I can learn and get better in excel...thanks...

[Aardigspook]

Welcome to the forum.
You say you want it to highlight when it 'does not exceed or less', which reads to me that you want it to be equal to - and for it to highlight in red if not equal to.
Select the range you want to conditionally format (P5:P100, let's say), click 'Conditional Formatting' then 'New Rule' then 'Format only cells that contain'. At the bottom of the dialogue box, leave 'Cell Value' as it is, change 'between' to 'not equal to' and enter '=SUM(A5:D5)' in the right-hand box. Click the 'format' button and choose the highlighting you want (red fill, red text, whatever), then click OK.

If you want 'equal to or less than', use <=
If you want 'equal to or greater than', use >=

Hope that helps.

[aqalna]

I appreciate the advice...and have been able to get it done...but how do I do it for each line...is there a way to drag and drop....so I can automate the process because right now I was able to do "does not equal to sum:a5:d5" but the next line I want it to be "does not equal to sum:a6:d6"... basically I was hoping to drag the condition and make it change...please let me know if you can help me further and thanks again!

[aqalna]

actually I think I figured it out by playing with the formulas

I attached a screenshot to illustrate what I did...

Capture.JPG

[Aardigspook]

Excel doesn't make it particularly clear, but Conditional Formatting will automatically change the cell reference (column and/or row) to match the range selected. So if you select the range you need (P5:P100, for example) then refer to row 5 in the CF formula (as you have) then when Excel looks at P6, it'll read the CF formula as 'Cell Value - not equal to - =SUM($A6:$D6)' and so on.

If you have set up CF for a small range and want to extend it (let's say you did just P5 and want to extend it to P5:P10) then there are two methods:
1. Select P5 and use the 'Format Painter' tool (beside the Paste button on the Home tab of the ribbon) to copy the formatting (which includes CF) to the other cells P6:P10.
2. Click 'Conditional Formatting' then 'Manage Rules', select the rule whose range you want to amend and change the range in the 'Applies to' box.

Hope that helps.