When the sum of B7-D7 is less than cell (I7) I would like to change the shade of B7-D7 to orange to indicate that larger values need to be entered to equal the value of cell I7. And when the sum of these three cells does equal I7 their color should change to green. I recorded two macros to change the colors and I've run them to verify they work. But I've got something off in my simple macro below.
Thanks for your help. David
Please Login or Register to view this content.
Why the need for VBA for this? Excel has a Conditional Formatting function that does this really easily
1. highlight the range you want to apply the conditional formatting to (B7:D7)
2. on the home tab, styles, select CF
3. select new rule, select use formula
4. enter =sum(B7:D7)<I7 Format fill ORANGE
Repeat 3 & $ with =sum(B7:D7)=I7 Format fill GREEN
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
Sorry for the tardy response. The reason for not using Conditional Formatting is that these cells need to be Protected with only allowance for entry/erasing of numbers. I tried the VBA code but it did not work for me, unfortunately.
CF will not interfere with any protection applied to a cell
dgibney,
If you really need code, then try this.
However, you didn't define what happens if the sum of the cells is greater than the value of cell I7. So, I suppose you will work that out yourself.
The code works, but I didn't test it under different conditions (so there may be side-effects). However, it will give you the result you want. But if the cells are protected, you will get run time errors and have to add code to unprotect the sheet while the code runs. Or change the code to a regular Sub. That is why Conditional Formatting would be best.Please Login or Register to view this content.
Put this code in the Sheet1 module. That way you don't need the "Activate Sheet1" line of code.
You also don't need the '$' while using vba. The '$' is for formula use only.
Best of luck to you.
Last edited by Jim885; 08-07-2014 at 07:57 PM.
If I helped in any way, please click the star
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks