Vba Help Please

[ccameron]

Hi Guys,
I have a piece of code that fills the active cell with colour but i would like it to work in a set range only and am not sure how to do this.
Any help would be very much appreciated. The range I would like it to work in is D8:O200

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Interior.ColorIndex = xlNone Then
Target.Interior.ColorIndex = 16
Else
Target.Interior.ColorIndex = xlNone
End If
End Sub

Thanks guys

 

[mikerickson]

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim oneCell as Range
    On Error Resume Next
    For each oneCell in Application.Intersect(Target, Range("D8:O200")
        With oneCell
            .Interior.ColorIndex = IIF(.Interior.ColorIndex=xlNone, 16, xlNone) 
        End With
    Next oneCell
    On Error Goto 0
End Sub

 

[ccameron]

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim oneCell as Range
    On Error Resume Next
    For each oneCell in Application.Intersect(Target, Range("D8:O200")
        With oneCell
            .Interior.ColorIndex = IIF(.Interior.ColorIndex=xlNone, 16, xlNone) 
        End With
    Next oneCell
    On Error Goto 0
End Sub

Thanks for the reply mikerickson,
I copied and pasted the code, but it comes up with an error.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim oneCell As Range
On Error Resume Next
For each oneCell in Application.Intersect(Target, Range("D8:O200")
With oneCell
.Interior.ColorIndex = IIf(.Interior.ColorIndex = xlNone, 16, xlNone)
End With
Next oneCell
On Error GoTo 0
End Sub

Not sure what it means.

 

[mikerickson]

It probably means that its missing a parenthesis.

 

[ccameron]

It probably means that its missing a parenthesis.

Sorry mike,
I would not know where to start looking, I am only a novice with this type of thing.

Regards
ccameron

 

[ccameron]

Hi Guys,
I still cant seem to get this problem sorted, if anyone can help I would appreciate it very much.
Thankyou in advance for any help given.

Regards ccameron

 

[mikerickson]

Every left parenthesis needs to be closed with it's matching right paren.

 

[ccameron]

Every left parenthesis needs to be closed with it's matching right paren.

Hi Mike,
Thanks for the reply
For each oneCell in Application.Intersect(Target, Range("D8:O200"))

Do you mean the extra bracket on the end, as above. I tried that and it still didnt work for me.

 

[Rick Rothstein]

I copied and pasted the code, but it comes up with an error.

For each oneCell in Application.Intersect(Target, Range("D8:O200"))

Do you mean the extra bracket on the end, as above. I tried that and it still didnt work for me.

Where did you copy the code to? Mike's code is a worksheet event procedure and, as such, must be copied to a worksheet module, not a standard module (where macro are copied to). The easiest way to bring up the worksheet code module is to right click the name tab at the bottom of the worksheet you want to have this functionality and select View Code from the popup menu that appears... copy Mike's code into the code window that opens up when you do that (and don't forget to delete the copy of Mike's code that is in the standard module).

 

[mikerickson]

I intended that the code I posted would go in the same place as the SelectionChange event posted in the OP. (After the OP code was deleted.)