Changing excel cells just by clicking

Microsoft Microsoft excel 2007 full vers...
September 22, 2010 at 11:06:01
Specs: Windows Vista
I have set up a worksheet to keep track on whether someone has turned in his/her work or not. If I remember correctly, there is some kind of IF formula that can be created to where if you click on the cell it will change color or maybe even put a symbol in the cell. If anyone knows how to do this, it would make my life a ton easier. Thanks

See More: Changing excel cells just by clicking

Report •

September 22, 2010 at 12:46:15
You can't use a formula to set the color of a cell or place a value in it just by clicking.

However, there is a VBA based solution.

I use this to place a 1 in an empty cell and set it's fill color to blue by double-clicking in it.

If the cell already contains 1, then a double-click will clear both the "1" and the color.

Note: This code is restricted to the Range D4:I68. Adjust as required.

Right click the sheet tab for the sheet you want this to happen in, choose View Code and paste the following code into the pane that opens.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Dim isect As Range
     Set isect = Application.Intersect(Target, Range("D4:I68"))
     If Not isect Is Nothing Then
         If Target.Value = "1" Then
             Target.Interior.ColorIndex = xlNone
             Cancel = True
             Target = "1"
             Target.Interior.ColorIndex = 37 'Pale Blue
             Cancel = True
         End If
     End If
 End Sub

Report •
Related Solutions

Ask Question