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

Try this;

Private Sub Worksheet_Change(ByVal Target As Range)
Dim LR As Long
LR = Cells(Rows.Count, "B").End(xlUp).Row

Set Rng = Range("B7:I" & LR)
  If Not Intersect(Target, Rng) Is Nothing Then
Application.EnableEvents = False
If Target.Value <> 0 Then Target.Value = "ü"
Application.EnableEvents = True
End If

End Sub

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;

If Target.Value <> 0 Then Target.Value = "ü"

to this

If Target.Value <> 0 Then Target.Value = "'ü"

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.

 Private Sub Worksheet_Change(ByVal Target As Range)
 
Set Rng = Range("B7:I18")
 If Not Intersect(Target, Rng) Is Nothing Then
 Application.EnableEvents = False
 If Target.Value <> 0 Then Target.Value = "'ü"
 Application.EnableEvents = True
 End If
 
End Sub

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

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("B7:I18")) Is Nothing Then Exit Sub
Application.EnableEvents = False
If Target.Value <> 0 Then Target.Value = "ü"
Application.EnableEvents = True
End Sub

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.