Assistance needed with VBA code please

[H100K]

Hello All,

I am currently using this VBA code below however, it means that all the cells change color but I only need it to apply to these range of cells E5:E26, F5:F26, J5:J22 and K5:K22 can anybody help with this?

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Cancel = True
Select Case Target.Interior.ColorIndex
Case xlNone, 4: Target.Interior.ColorIndex = 3
Case Else: Target.Interior.ColorIndex = 4
End Select
End Sub

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
Cancel = True
Target.Interior.ColorIndex = xlNone
End Sub

Thankyou!

 

[Joe4]

Welcome to the Board!

Try something like this:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

    Dim rng As Range
    
    Set rng = Range("E25:F26, J5:K22")
    
    If Not (Intersect(Target, rng) Is Nothing) Then
        Cancel = True
        Select Case Target.Interior.ColorIndex
            Case xlNone, 4: Target.Interior.ColorIndex = 3
            Case Else: Target.Interior.ColorIndex = 4
        End Select
     End If
     
End Sub

 

[H100K]

Hello Joe4,

Thankyou for the warm welcome!

This works great just one thing if I wanted the option to change back to white could this be added in the code please?

Thanks again!

 

[Joe4]

Hello Joe4,

Thankyou for the warm welcome!

This works great just one thing if I wanted the option to change back to white could this be added in the code please?

Thanks again!

If you mean from your second procedure, you should be able to apply the exact same logic to that that I used in the first one.
Give it a try and see how it works! It will be a good test to see how well you understand the method I used.
Write back if you have questions.

 

[H100K]

Thankyou Joe,

Ended up tweaking yours slightly and it works fine!

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

Dim rng As Range

Set rng = Range("E5:F26, J5:K22")

If Not (Intersect(Target, rng) Is Nothing) Then
Cancel = True
Select Case Target.Interior.ColorIndex
Case xlNone, 4: Target.Interior.ColorIndex = 3
Case Else: Target.Interior.ColorIndex = 4
End Select
End If

End Sub


Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)

Dim rng As Range

Set rng = Range("E5:F26, J5:K22")

If Not (Intersect(Target, rng) Is Nothing) Then
Cancel = True
Select Case Target.Interior.ColorIndex
Case 4, xlNone: Target.Interior.ColorIndex = xlNone
Case Else: Target.Interior.ColorIndex = xlNone
End Select
End If

End Sub