Convert Conditional Formatting to VBA

[dosbirn]

I recorded a macro to get this code for conditional formatting. It works in the spreadsheet but not in VBA:

Please Login or Register  to view this content.

Cells L2 through L7 have formulas. Cell D7 is a formula. I need the summed values of cells L2 through L7 compared to the value of cell D7. If they total the same amount, I don't want any conditional formats set. If the totals don't match, I want conditional formatting to fill the interior of cell D7 red and change the font color to black (default is red). (This lets me know there is a calculation error somewhere in my spreadsheet).

Any suggestions?

Thanks, Debbie

[Solus Rankin]

try:

Please Login or Register  to view this content.

[dosbirn]

Thanks, Solus, but unfortunately that gives me the same result mine did. It turns D7 cell red even though the sum of L2:L7 equals the amount in D:7. D7 should not turn red unless it meets this condition: =L2:L7<>D7

Could the problem be because I'm trying to compare the results of the formulas instead of the actual values?

[Solus Rankin]

Yes. That would be the problem.

[dosbirn]

Okay, then would you know how to change this code (formula) so that it inserts the value in cell L2 instead of the formula?

Please Login or Register  to view this content.

Thank you for your help with this, Solus.

[Solus Rankin]

The easiest way is probably just using a helper cell, one in a column you can hide. Like:

Please Login or Register  to view this content.

This lets the calculation happen in one cell and then passes the value to the cell you're using your conditional formatting for.

[dosbirn]

Okay, Solus, using your suggestion, I placed a formula in (helper) cell L8, summing L2:L7. (I didn't have to remove all of the formulas in cells L2 thru L7 after all). Then I changed the cell reference in my recorded conditional format (in my original post above) from "=L2:L7<>D7" to "=L8<>D7" and now it turns cell D7 red if L8 and D7 do not equal...and it does not change it red if the totals match. So I'm good to go!

I tried to change the cell references in the code you gave me above but I couldn't get it to work right. I'm sure it was because I wasn't changing it correctly. But at least my macro works now.

Also, I had to keep the helper cell within my print range or the code I have for the "set print area" wouldn't select the correct range to print.

Thanks SO MUCH for your help, Solus!