Completing of code...

Last Edited By Krjb Donovan
Last Updated: Mar 05, 2014 09:19 PM GMT

Question

I have been using another expert and she has been amazing but she has gone away and I need some help finishing a bit of code!

The code she gave me works perfectly:


Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim R As Range Set R = ActiveSheet.Range("G6:G26") For Each c In R

If c = "N/A" Then
  MsgBox "This option is not available at the centre you have chosen. Please choose an alternative option"
  c.Offset(0, -2).ClearContents
End If

Next c

End Sub

But now I want to have 3 ranges: G6:G26, L6:L26 & Q6:Q26 but can't figure out how to get this to run over multiple ranges.

My VBA is complete beginner so please help!!

Answer

Like this:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim R As Range Set R = ActiveSheet.Range("G6:G26,L6:L26,Q6:Q26") For Each c In R If c = "N/A" Then

 MsgBox "This option is not available at the centre you have chosen. Please choose an alternative option"
 c.Offset(0, -2).ClearContents

End If Next c

End Sub

Advertisement

©2017 eLuminary LLC. All rights reserved.