recalculating values across non continuous ranges

Hello Excel Forum!

Looking for some advice. I've got a macro enabled spreadsheet that is some 2100 rows tall with a 7 line repeating pattern starting at E2. It looks like this:

Daily regular order product A -25
Daily custom order product A -13
Daily regular order product B -500
Daily custom order product B -1000
New stock product A
New stock product B
Total usage -1538

How can I program VBA so that whenever an entry is made in rows 1 through 4 of the pattern, they are automatically converted to negative? For example, if i make an entry on row 989, that is row 1 in the pattern and should be changed to negative, but an entry on 986 through 988 should not be converted. Also, simply formatting as negative is not sufficient. The values on all seven rows are used elsewhere.

The data on the sheet is archived and wiped each month, so I don't think an ordinary formula would work. Any ideas?

Thanks,
David

I've attached the worksheet. Other than summing each day's activity on row 7 of the pattern and a grand total on the right side, there is not much happening here. I would think I'd be on the right track with code like this, but i'm getting a type mismatch error on my IF line.

Please Login or Register  to view this content.

Perfect. Thank you!

Well, sadly i counted my chicken before he was fully hatched. Can definitely see a beak and a head though

For the first group, it works flawlessly. I enter a number and if it is supposed to, it converts to a negative and everything's good. In the other rows, i'm getting unexpected behavior.

Take cell N11. If I enter say 5 in that cell, I see that excel is changing the number to negative (good) but also changing the format from general to date (not good). It is also changing rows 6 and 7 in the pattern to date formatting as well. From what i see in the code, i'm only telling it to change the value, so what could be causing this?

Also, once a value is entered for a cell in the range, you cannot clear the cell unless you've selected multiple cells. I don't want this to fire when deleting (eg a user enters a value in the wrong cell).

Thank you Skywriter.

The fact that the behavior did not duplicate for you helped me narrow down the problem. Somewhere in my "personal codebook" was a script that was also firing and causing the issue. I stripped it out and the code is behaving perfectly in all the rows and cells. Thanks!