How to apply macro to a range, even if additional rows are added

Try this;

Please Login or Register  to view this content.

Originally Posted by Lowa

Thanks but this didn't limit the macro to the above range (the number of rows it abblied to did not stop at row 18), and also a tick only appeared in columns C - I if there was already one in column B on that particular row

Of course it doesn't limit the range to row 18 anymore. You didn't want it to. You wrote;

Originally Posted by Lowa

when I add a row inside the above range the macro does not apply to the whole range anymore.

I would like to know if there is any way that I could apply this macro to the cells within the above range, including any additional rows that are inserted within the range.

So, it sounded like you wanted the range to expand as your data rows expand. That's what I gave you.

Secondly, your code has nothing to do with tic marks.
If you want tic marks to appear, then change this;

Please Login or Register  to view this content.

to this

Please Login or Register  to view this content.

If you are inserting rows between row 7 and 18 and you have more rows under row 18 that you don't want the macro to affect, then each time you insert another row between row 7 and 18 you will have to manually adjust the code to accommodate for the extra row. I see no other way around this.

So you can use this code and simply change the number highlighted in red to adjust for the newly inserted row(s) as you add them.

Please Login or Register  to view this content.

I see that you got the below code from TMS in this thread: http://www.excelforum.com/excel-prog...ple-cells.html

Please Login or Register  to view this content.

I don't know why you would have changed it to what you did in your first post in this thread. It seemed to work perfectly as TMS wrote it.
So again, if you add rows then just change the value of I18 as you need.

Originally Posted by Lowa

I copied and pasted your original reply that had B7:I in the range instead of B7:I18. I guess the macro stretched down so many rows because I didn't have '18' after the 'I' in the range.

Yes, the first code I gave you allows the range to be dynamic (the range will extend / grow to the last row even after new rows are added, time and time again). Whereas, B7:I18 is a fixed range that never changes. Thus, if you add/insert a row between row 7 and row 18, row 18 will become row 19 and will no longer be included in the original rage of B7:I18

Originally Posted by Lowa

Also I have the 'ü' character in my code because I changed the font in the selected cells to 'windings', which results in a 'tick'.

OK, so the code is correct, you just had to format the font of the cells in the range of B7 to I18 as windings to achieve the tic mark.

Originally Posted by Lowa

Anyway, you have a point I could just manually change the '18' in the range to whatever row I want it to finish on.

Thankyou for your help

If you think of any way to automate the process please let me know

You are welcome. Please mark this thread as SOLVED.
Best of luck with your project.