VBA to SHADE cells based on: step-size & starting cell

**zusammen** · 03-04-2009, 08:09 AM

Hello Forum Members,

I have a spreadsheet that will monitor payment schedules, in which both payment frequency and the payment start date are inputted by the user.

As such, to make filling out the column(s) fool-proof, I want to grey out cells in which data should not be entered.

For example, if the payment frequency is every 6th day, and the payments are to begin on day 0, then days 0, 6, 12 (etc) should be left white, whereas the remainder of the cells should be shaded.

I can achieve this using multiple conditional formatting rules in excel2007 with iterations of formulae of the type:

Please Login or Register  to view this content.

(where P5 and P7 are user input cells)

(see attached sample .xlsx sheet and image of same)

I would like a VBA solution so that it can be used in earlier versions of excel that don't have the advantage of >3 CF rules.

Ideally, I would like the following to be achieved:

1. VBA for the above mentioned, based on the 2 user-inputs (payment frequency and start date);

2. is it also possible to not only shade the appropriate cells, but to render them locked such that no text can physically be entered? ;

3. a separate VBA that allows the user to reset the appropriate column back to default state (ie, remove all shading, delete all entered text)

I am new to VBA, so I would greatly appreciate it if someone could get me started and point me in the right direction. I have scoured numerous 'alternate shading' posts but I can't seem to find something that is closely related to what I want to do.

Many thanks for any advice.

Z.

**zusammen** · 03-06-2009, 05:34 PM

bump no response

**Leith Ross** · 03-06-2009, 05:39 PM

Hello zusammen,

Since you want the code solution to pre Excel 2007 compatible, you should post a copy of the workrbook in Excel 2003 format.

**zusammen** · 03-06-2009, 06:12 PM

apologies, i actually thought it would be easier for people to see what i wanted if they had the benefit of all the conditional formatting rules present.

I have now attached the relevant .xls file in the original post.

Regards,

Z.

**Leith Ross** · 03-07-2009, 05:05 AM

Hello Zusammen,

This workbook has four buttons on Sheet1. Two of the buttons are for Example 1 and the other two are for Example 2. The buttons post and clear the schedule. The sheet is password protected allowing only the ungrayed cells to accept data. When a schedule is cleared , all cells are locked. The password is "1234". This is defined as a Constant at the top of the module. You can change this what ever you like. This has been added to the attached workbook.

Module1 Macro Code

Please Login or Register  to view this content.

**zusammen** · 03-08-2009, 06:55 AM

Hi Leith,

Firstly, thank-you kindly for your effort-it's exactly what I need!

I have been digesting what you wrote and playing around with it in excel2007.

Consequently, I have a couple of questions:

1. under what circumstance should the msgbox "schedule number not found" pop up? In testing various combinations, I tried to post a schedule by leaving both inputs for 1 of the cases blank; the only thing I managed to do was to freeze excel (required restart for everything to work again properly) and get the error message:

run-time error '-2147417848 (80010108)':
Method 'colorindex' of object 'interior' failed

Despite this being an illogical input from the user (ie, both inputs blank), can we modify the code to ensure that if it did occur, it wouldn't result in error?

2. I am a little confused about the worksheet protection.

Firstly, when I try and change the password "1234" and re-run the macro, I get the error:

run-time error '1004':
the password you supplied is not correct. verify that the caps lock key is off.....etc

Only if I re-enter "1234" in the VB editor does it accept the change and function properly. No other text seems to work...?

Secondly: since the majority of my working sheet will be locked, I noticed that if i pre-lock the sheet and make available only the relevant user-input cells, the macro does not work. On the flip side, what you coded works fine, but i'd prefer not to have to re-enter a password everytime the columns are reset.

Is there a way to still enable the relevant cell locking as per your coding, but at the same time ensure that the remainder of my working sheet can be simultaneously locked in other relevant (non-related) cells?

3. Lastly, if you have the time, could you please help me to code (macro) one final thing: once the relevant schedule has been posted, and the user inputs various numerical values into the appropriate cells, I would like to export the text results of columns G, H & J to a new worksheet such that a simplified & condensed version can be available for optional printing.

(or am I better of using an index-match approach to this?)

Again, I greatly appreciate you taking the time to impart your experience to me (and the rest of the forum)!

Zusammen

**Leith Ross** · 03-08-2009, 11:41 AM

Hello zusammen,

In answer to question number 1, the message box warning is there out of habit. If you want to remove the Case Else and the Msgbox lines, it isn't a problem.

The runtime error you got under 2007 is something I can't help you with. 2007 is not 100 percent compatible with previous versions of Excel. I don't have 2007 and can't tell you why your getting the error. Perhaps someone else in the forum can.

The password is hardcoded to allow the program to protect and unprotect the worksheet without dialogs. The drawback is you can't change the password from Excel menu. This is pretty much an either or situation, unless 2007 has a way around this, you can't it both ways.

In reference to your last question, I can add another command button that will copy the data the user has entered to another sheet. The question I have would this be just for the current information entered or would it keep a running history of the entries?

**zusammen** · 03-08-2009, 06:03 PM

Leith,

|| WRT to runtime error(s) when trying to update the schedule with zero entered into the payment schedule, I also tried 2 other things (which didn't work-excel still freezes):

1. saving the .xls back to .xlsx in excel2007
2. running yr original file .xls in excel2002

Rather than trying to figure out why it freezes, I was wondering if it would be easier just to hard code in a situation whereby zero cannot be accepted/entered into the payment schedule?

If you are still stumped on this one, I can post a separate thread on this...

|| WRT trying to change the password from "1234" to anything else in the VB editor: I discovered that if I started afresh with a new worksheet and VB module, I could successfully code in a new password.

|| Lastly, to answer yr Q: the export to a new sheet only requires transfer of the currently entered data; no running history is needed.

Regards,

Z.

**mikerickson** · 03-08-2009, 10:20 PM

No VB is needed, one conditional formatting formula will work for example 1

=OR(MOD($G4-$O$7,$O$5)<>0,$G4<$O$7)

**Leith Ross** · 03-08-2009, 10:31 PM

Hello zusammen,

Your problems are with Excel 2007. The macro works without problem in the Excel 2003 version. Excel 2007 is NOT 100% back compatible with previous versions of Excel. Saving from 2007 to a previous version of Excel is still no guarantee of compatibility. You are better off in this case using Mike's suggestion of no VBA. This will also allow you to set the password using the menu.

**zusammen** · 03-10-2009, 07:54 AM

Hi Guys,

Fantastic Mike! -that makes my life so much easier!

Perhaps I am being pedantic, but I need a slight adjustment. In combination with Mike's formula, my sheet now includes a modified version of Leith's 'clear schedule' VB such that I can also clear all formatting and text as follows:

Please Login or Register  to view this content.

I kept color "48" because I would like the default cleared cells (with exception of O5,7 & P5,7) to remain greyed out (in the color GREY) until a new schedule is set.

However, combining this with Mike's CF formula results in 'grey on grey' when a schedule is cleared and then renewed.

How can I keep Mike's CF but at the same time return a greyed out default cleared schedule?

I have tried numerous permutations etc but to no avail...I assume the answer lies in an additional CF, or modification of the original CF?

I have attached the worksheet in case this is as clear as mud.

Thanks as always.

Z.

**zusammen** · 03-12-2009, 08:24 PM

bump no response

**zusammen** · 03-12-2009, 08:29 PM

can anyone help me out here?

better yet, can anyone also assist in debugging the original VB (file: "alternate shading question_040309 ver 1.xls" from Leith's response) such that excel does not freeze when zero is entered into the payment schedule?

FYI: I am not convinced it is an excel2007 issue, because it gives the same error in multiple versions of excel on multiple machines. Additionally, I also repasted the code into a fresh spreadsheet, but no such luck.

Many thanks for pushing me in the right direction....

**Leith Ross** · 03-13-2009, 12:14 PM

Hello zusammen,

I have downloaded the sample file and the only differences are you removed 2 buttons and all the cells are gray. Either a light gray or dark gray depending on if the cell is a payment or not. Entering zero does not cause my system to freeze. Prior to you altering the code, did the macro "freeze" when you entered zero? I still have the original file and there is no freezing of Excel on my machine. I am running Windows XP and Excel 2003. If it runs on my machine, it should run on any other running Excel 2003.

**zusammen** · 03-13-2009, 03:43 PM

Hi Leith

This is terribly frustrating

I am slowly learning the basics, and I have already learned a lot just from playing with your example. (my first use of a macro!)

I refer to your original unadulterated attachment: running on XP with Excel2002 or 2007 at my end causes it to freeze.

perhaps someone else out there could see if they suffer the same problem and post back??

You are 100% correct in what you observed with the new file: "grey on grey", which is the problem I refer to. Since using Mike's CF rule avoids the whole freeze issue (for now) I am experimenting with it; BUT I would like to achieve the exact result that your VB gives (grey when clear, white when posted) while at the same time using Mike's CF rule and my 'clearcontents' VB.

It seems overlap of the two causes "grey on grey" - I merely used 2 shades of grey in the attached example so that the "grey on grey" was more noticeable. Can you suggest a way around this??

Or can you please suggest some VB that will not allow the user to enter zero (since logically they wouldn't) into O5 and P5 in your original solution?

Z.

**Leith Ross** · 03-13-2009, 07:53 PM

Hello zusammen,

One thing to check would be your project references. Perhaps some library isn't loaded on your side.

1. Alt + F11
2. Press ALT+ T
3. Press R

This will bring up a list of references. Check if anything is marked "Missing:"

**zusammen** · 03-14-2009, 10:09 AM

Leith,

It all seems to be there (i scrolled thru the list and did not see 'missing' anywhere), and there are 4 default checked items in the list (see attached screen shot).

Regards,

Z.

VBA to SHADE cells based on: step-size & starting cell

LinkBack

Thread Tools

Rate This Thread

Display

VBA to SHADE cells based on: step-size & starting cell

Re: VBA to SHADE cells based on: step-size & starting cell

Re: VBA to SHADE cells based on: step-size & starting cell

Re: VBA to SHADE cells based on: step-size & starting cell

Re: VBA to SHADE cells based on: step-size & starting cell

Re: VBA to SHADE cells based on: step-size & starting cell

Re: VBA to SHADE cells based on: step-size & starting cell

Re: VBA to SHADE cells based on: step-size & starting cell

Re: VBA to SHADE cells based on: step-size & starting cell

Re: VBA to SHADE cells based on: step-size & starting cell

Re: VBA to SHADE cells based on: step-size & starting cell

Re: VBA to SHADE cells based on: step-size & starting cell

Re: VBA to SHADE cells based on: step-size & starting cell

Re: VBA to SHADE cells based on: step-size & starting cell

Re: VBA to SHADE cells based on: step-size & starting cell

Re: VBA to SHADE cells based on: step-size & starting cell

Re: VBA to SHADE cells based on: step-size & starting cell

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions