Generate messageBoxes over a range rather than individual cells to reduce code

[mattbro]

Hi,
I have a workbook containing a sheet for each letter of the alphabet. Each row relates to an item distributed. I have the distribution date in column A. In column B I have the 'Today' function. Column C is days lapsed using e.g. C2=B2-A2, formatted to a single number. I have a 27th page called 'Alarm, in which all the data from Column C of all pages is fed. Each cell is coded using the code

Please Login or Register  to view this content.

And so on, from A2 to A200 all the way to Z2 to Z200. Thus by selecting the alarm page, I can see which items have gone over 7 days.

The trouble is;
a) This is a massive load of spaghetti code
b) It is too much data for the 64k limit for the VB editor in Excel 2003.
In the worse case scenario, I do want all those message boxes, as once the time has lapsed, I would chase it up and change the date sent to the date chased (thanks for the prompt, TMShucks).
If anyone can give me any pointers as to how to chop this code down to size, I would be really grateful.
Many thanks,
Mattbro

[TMS]

You'd loop through all the cells in the range At to Z200 ... but do you want all those message boxes (in the worst case)?


However ...

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here

[royUK]

Conditional Formatting would make more sense

[TMS]

I agree with Roy on th CF.

However, if this is what you want, try:

Please Login or Register  to view this content.

Regards, TMS

[mattbro]

Thanks guys-I looked at conditional formatting, and it is great, but the looping code seems to work better. I had to change the code slightly omitting the 'END IF' to;

Please Login or Register  to view this content.

Job done. Many thanks

[royUK]

I'm not sure haow the code can be better. Conditional Formatting will be faster, less annoying - message boxes need to be clicked and the code won't work if macros are not enabled

[TMS]

Can't argue with Roy's logic.

You didn't have to change the code, you chose to change it ... it worked as delivered. But no worries, you have to maintain it.

Regards, TMS

[mattbro]

Well RoyUK, I guess you would have to see it in-situ. The truth is, I really needed to see the code for other applications where I am performing similar roles-with copious quantities of spaghetti code, and I imagine this will take another dimension when I get asked to start adding further conditions;
At the moment, there is one category of turnaround time-seven days. Both methods work fine for this. Shortly, I expect I will get asked to have alarms for different categories-items to alarm at 7days, different items to alarm at 8 days, 9 days and 10 days all based on the date sent (I know it's going to arrive on my desk...I can just see it coming). Conditional formatting looks better for this except I need all pages to feed into the 'alarm' page so that though all staff can enter the data, only one page needs to be looked at for all the alarms. I can't see how I can use conditional formatting in the extended scenario, but I may be able to modify the code to facilitate an extra parameter....or force the staff to code by category rather than by A-Z. If you have any ideas, I would be delighted to hear them.
M

[mattbro]

Hi TM Shucks. I was getting an error message on the final 'END IF'. By changing the code, the error message disappeared and all was fine....