Changing a cell color just by clicking & changing it back to the previous color.

[FrancisM]

In a post dated June 2006 phxsportz provided code to change cell color by clicking it.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Cancel = True
    Target.Interior.ColorIndex = IIf(Target.Interior.ColorIndex >= 3, -4142, 3)
End Sub

I tried the code it did what it was supposed to do, but it worked on every sheet & cell that was clicked.

I was wondering if it is possible to do the following to a selected group of cells on a certain worksheet. Click on the cell & it turns red, double click & it returns to the previous color, and not the default color.

 

[Rick Rothstein]

In a post dated June 2006 phxsportz provided code to change cell color by clicking it.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Cancel = True
    Target.Interior.ColorIndex = IIf(Target.Interior.ColorIndex >= 3, -4142, 3)
End Sub

I tried the code it did what it was supposed to do, but it worked on every sheet & cell that was clicked.

I was wondering if it is possible to do the following to a selected group of cells on a certain worksheet. Click on the cell & it turns red, double click & it returns to the previous color, and not the default color.

Yes, the code you posted works on every cell, but no, it doesn't work on every worksheet, only on the worksheet whose module you put it in. Now, to your question... what exactly do you mean by a "selected group of cells"?

 

[FrancisM]

Sorry about not being very clear. I need it to work on just a range of cells. In particular J3 to J9 & J12 to J16. The fill color I am using is not the default white. This will occur on one sheet, J3 to J9 & J12 to J16 the following sheets will be conditionally formatted based on the previous sheet.

 

[Rick Rothstein]

Sorry about not being very clear. I need it to work on just a range of cells. In particular J3 to J9 & J12 to J16. The fill color I am using is not the default white. This will occur on one sheet, J3 to J9 & J12 to J16 the following sheets will be conditionally formatted based on the previous sheet.

If the color in those cells are provided by Conditional Formatting, then those colors cannot be overridden (either manually or by VBA code). The only way to change the color of a Conditionally Formatted cell is to remove whatever caused the Conditional Format to be applied or to delete the Conditional Formatting rule.

 

[FrancisM]

Thanks for responding so quickly. It was worth a try.

 

[Rick Rothstein]

Thanks for responding so quickly. It was worth a try.

If you want to highlight the cells in some way in response to a double-click, you could consider placing a fill pattern in the cell or placing a thick border around them, or making the font bold, or any other property change that is not provided for by conditional formatting.

 

[Veritan]

If the original code you provided is working as intended, and the only change you want to make is to limit which cells the Double-Click event applies to, you could try something like this:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    With Target
        If .Column <> 10 Then Exit Sub
        If .Row < 3 Or .Row > 9 Then
            If .Row < 12 Or .Row > 16 Then Exit Sub
        End If
        Cancel = True
        .Interior.ColorIndex = IIf(.Interior.ColorIndex >= 3, -4142, 3)
    End With
End Sub

I'm sure that an Intersect method would work as well, but I haven't personally used that very much myself.

 

[Rick Rothstein]

If the original code you provided is working as intended...

I think the OP meant working as intended by the programmer but not as needed. Message #3 mentioned the cells are colored by Conditional Formatting and, as I responded in Message #4, you cannot override the colors provided by Conditional Formatting.