How to make a singular macro apply to multiple cells?

I have a range of cells where I would like a tick to appear when I type anything in the cell.

At the moment I have the following macro which works:

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = "$B$2" Then
If Target.Value <> 0 Then Range("B2").Value = "ü"
End If

End Sub

With this macro if I type anything other than "0" in cell B2, the value in cell B2 will change to a tick.

I would like to apply this macro to multiple cells, e.g. if I type anything in cell D5 I want cell D5 to change to a tick. if I type anything in cell F13 I want cell F13 to change to a tick. Etc

I understand that I could just copy the macro over and over again and change each cell value but that would be time consuming.

I am wondering if I can change the target address and the target value to cell "n" so that any change in a cell will only result in a tick appearing in that particular cell.

Also just thought I'd add that the cells I would like to apply my macro to range from B2 till I18

Please use Code Tags

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False
If Target.Value <> 0 Then Target.Value = "ü"
Application.EnableEvents = True

End Sub

Regards, TMS

You can try by adding or method in IF condition

If Target.Address = "$B$2" or Target.Address = "$D$5" or Target.Address = "$F$13" Then
If Target.Value <> 0 Then Target.Value = "ü"
End If

End Sub

Hi,

If you want to apply this on specific cells only (not whole sheet's cells), then define a range and use intersection() :

Private Sub Worksheet_Change(ByVal Target As Range)
  Set rng = Range("B2,D5,F13")
  If Not Intersect(Target, rng) Is Nothing Then
     If Target.Value <> 0 Then Target.Value = "ü"
  End If
End Sub

Please note that, if you want a Worksheet Change event handler to make changes to the worksheet, you must use Application.EnableEvents = False before making the change(s) and Application.EnableEvents = True after, or the event handler could be executed more than once and even loop.

That applies regardless of whether you want to apply the event handler to a single cell, several or many specific cells, or all cells on the worksheet.

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False
If Target.Value <> 0 Then Target.Value = "ü"
Application.EnableEvents = True

End Sub

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

Regards, TMS

Not a good idea to go back and add detail to the original post. It can easily get overlooked ... and it can make nonsense out of replies you have already had.

BUT ... please add Code Tags to your code excerpt in your original post (see below).

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

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code between [CODE] [/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here



(This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)

You're welcome. Thanks for the rep.

Thanks for editing the first post and adding code tags.

when my thread is solved should I note which reply solved my question?

No, there's no specific way to acknowledge which post(s) resolved your issue (in this forum).

You can mark the thread solved, as you have, and you can give positive rep to the contributor(s), as you have, but there's no "best answer" or "best solution" option. And, of course, you can acknowledge, in the thread, which solution you opted to go with, as you have

Not a bad first thread, all in all.

Regards, TMS

Hey guys,
I have a similar problem to the above though the code is very different. Right now the code just applies the macro to a singular cell B3 and I am hoping to apply it to any cell on the sheet that I click on. I have pasted the code below if you guys could kindly take a look:

Dim StopTimer           As Boolean
Dim SchdTime            As Date
Dim Etime               As Date
Const OneSec            As Date = 1 / 86400#




Private Sub ResetBtn_Click()
    Range("B3").Interior.ColorIndex = -4142

    StopTimer = True
    Etime = 0
    [B3].Value = "00:00:00"
End Sub

Private Sub StartBtn_Click()
   Range("B3").Interior.ColorIndex = 6
   StopTimer = False
   SchdTime = Now()
   [B3].Value = Format(Etime, "hh:mm:ss")
   Application.OnTime SchdTime + OneSec, "Sheet1.NextTick"
End Sub



Private Sub StopBtn_Click()
    Range("B3").Interior.ColorIndex = 4
    StopTimer = True
    Beep
End Sub

Sub NextTick()
   If StopTimer Then
      'Don't reschedule update
   Else
      [B3].Value = Format(Etime, "hh:mm:ss")
      SchdTime = SchdTime + OneSec
      Application.OnTime SchdTime, "Sheet1.NextTick"
      Etime = Etime + OneSec
   End If

End Sub