Search code examples
excelvbaformattinghighlight

Highlight Selected Cell in Excel without Clearing Cell Formatted Color

I have an Excel workbook with many sheets where we have cells formatted green for the respective personnel to input information. I would like to highlight the cell yellow when clicking on it and then return to the base green when clicking out. If another cell has no formatted color, I do not want that to turn green if accidentally clicking on a non-colored cell.

Let's say the data we want to enter is in Sheet 1, cells A2:D20. A1:D1 have no color and represent headers for the columns. If someone clicks on them, I would like it to highlight yellow but return to no formatted color. If they select anywhere in A2:D20, it highlights yellow but returns to the previously formatted color of green.

Thanks!

Solution

  • Select the range of interest and add a conditional format using this formula:

    =CELL("address")=ADDRESS(ROW(),COLUMN())

    In the worksheet code module add this:

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.CountLarge = 1 Then Target.Calculate
End Sub