0

I feel this is possible, but I have been unable to get anything working using conditional formatting and am not quite sure what direction to take in VBA.

I want to highlight cells across a row,N2 through AN2, green if the value of the cell plus previous cells is less than or equal to the value of another cell, C2.

If C2 is 1,000, I would want N2 at 300 highlighted green (cumulative total 300), O2 at 600 highlighted green (cumulative total 900), P2 at 100 highlighted green (cumulative total 1,000), and Q2 at 150 not highlighted as the cumulative of it plus the previous cells is 1,150, above the 1,000 in the reference cell.

Can this be accomplished through conditional formatting or what should I do in VBA to make this happen?

Thank you.

0

Assuming that cell M2 is empty, then you want to highlight any cell that is less than or equal to the value in C2 minus the sum of cells to the left of this cell beginning at M2. You can do that with Conditional Formatting using the formula

=$C$2-SUM($M2:M2)

Select the range you want to format (N2:AN2) and set Conditional Formatting as shown below:

enter image description here

If cell M2 is not empty, you can still make this work, but you need to use a slightly more complicated formula in the Conditional Formatting rule:

=$C$2+N2-SUM($N2:N2)
  • @Blackie, the second formula will color Q2 also. Won't $C$2-SUM($N2:N2) do the job regardless of what's in M2? – Bandersnatch Jan 9 '18 at 16:29
  • @Bandersnatch I double-checked and the second formula does not highlight Q2. Changing the formula to $C$2-SUM($N2:N2) results in N2 being highlighted and not O2:Q2, which is not correct. – Blackwood Jan 9 '18 at 16:41
  • @Blackie, I couldn't get this to work correctly at ALL in Excel 2011 for Mac. I could highlight the first 3 cells if I used a formula of =IF(N2<=$C$2-SUM($L2:L2),1,0), but when I changed the range to $K2:K2, it highlighted all 4. $M2:M2 highlighted 2, and $N2:N2 highlighted 1. At least there's a pattern, but sheesh! it makes no sense. So if your approach works, more power to you and good luck to OP. :-) – Bandersnatch Jan 9 '18 at 17:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy