Double-Click to Change Interoir Color
I used the following technique, which also seems to work well:
1. Add another blank worksheet to your workbook.
2. Name the new worksheet "Color Mask".
3. Select both your data worksheet and the "Color Mask" worksheet.
4. Ctrl-select all of the cells you mentioned ("D12", "H12", "D14", "H14",
etc.).
5. Apply your gray color to those cells.
6. Unselect the 2 worksheets.
Now when you double-click on your worksheet, it will simply check to see if
the "Color Mask" worksheet has a color in the same cell as the Target cell.
If it does, then the routine clears the color on the entire row and copies
the color on the "Color Mask" worksheet to the Target cell. You can make
the colors any color you want, and easily change the target cells by
changing those cells on the "Color Mask" worksheet. No changes to the code
are required.
This routine will clear the color of other cells on the same row, however,
so if you had cell A12 set to Red, it will be cleared once you double-click
on cell D12 or H12. I don't know if you need to retain colors in the other
cells that are not in columns D or H on the same row.
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _
Cancel As Boolean)
Dim wsColorMask As Worksheet
Dim rngColorMaskTarget As Range
On Error GoTo ExitSub
Application.EnableEvents = False
Set wsColorMask = ThisWorkbook.Worksheets("Color Mask")
With Target
Set rngColorMaskTarget = wsColorMask.Cells(.Row, .Column)
End With
If rngColorMaskTarget.Interior.ColorIndex < xlNone _
Then
With Target
'Clear the color of the entire row first.
.EntireRow.Interior.ColorIndex = xlNone
'Now set the color of the Target cell to be the
'same as that on the "Color Mask" worksheet.
.Interior.ColorIndex = rngColorMaskTarget.Interior.ColorIndex
End With
Cancel = True
End If
ExitSub:
Application.EnableEvents = True
End Sub
--
Regards,
Bill Renaud
|