conditional formatting

  • Thread starter Thread starter KC
  • Start date Start date
K

KC

I am creating a simple check sheet which shows what task items have been
checked. The boxes have been formatted to turn a color when a value greater
than zero has been entered. My question - Is there a way to change the color
by just clicking within the cell with your mouse or is it maditory to input a
value in the cell?
 
You can try this event macro. Assume the range of interest is A1:A10.

Delete any conditional formatting you already have applied to this range.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("A1:A10")) Is Nothing Then
If Target.Interior.ColorIndex = 36 Then 'light yellow
Target.Interior.ColorIndex = 0 'white (default)
Else
Target.Interior.ColorIndex = 36
End If
End If
ws_exit:
Application.EnableEvents = True
End Sub

When you select any cell within the defined range it will fill with a light
yellow color. If you select that cell again it will remove the yellow fill
and return back to white (the default).

To install this macro:

Select the sheet where you want this to happen
Select the sheet tab and select View code
Paste the code into the window that opens
ALT Q to return to Excel
 
Thanks for your quick reply T. Valko. Macro works great but is it possible
for the same macro to add a value of X while changing the color for that
cell?
 
Ok, this will place a "X" in the cell and color the cell light yellow when
selected.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("A1:A10")) Is Nothing Then
If Target.Interior.ColorIndex = 36 Then '36 = light yellow
Target.Clear
Else
Target.Interior.ColorIndex = 36
Target.Value = "X"
End If
End If
ws_exit:
Application.EnableEvents = True
End Sub
 
Back
Top