Changing cell color by single click

ActiveCell.Value = NewVal

this might work for you. you need to give the calendar a range name (I used 'ColorRange' for this example). This code has to go in a sheet, not a module (right click on the sheet's tab and select 'View Code')

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
If Not Intersect(Target, Range("ColorRange")) Is Nothing Then
If Target.Interior.ColorIndex = 34 Then
Target.Interior.ColorIndex = 0
Else
Target.Interior.ColorIndex = 34
End If
End If
End Sub

HTH

Mark

ActiveCell.Value = NewVal

Thanks Mark for that information, I took your code and changed it a bit to enter a value of 1 or "" intead of the color codes and its almost perfect. I need this so that the number of days can be added, and the conditional format changes the color of the cells depending if there is a 1 or "" in as the value. Can this code be adjusted so that that clicking the cell once changes the value and clicking it again changes it back? I only ask because in order to change it back I have to click another cell then go back to that cell to reset it. Just curious if there was a way, but it works MUCH better than before! Thanks!

Use Hot-key Macro

To work this copy the code below and paste to your Macros. Then select each macro and assign a Hot-key to each with Macro-Options, I used Ctrl-s for the 1st for Select and Ctrl-u for the 2nd for Un-Select. The first will yellow your selected cell when you press Ctrl-s and the second will return the cell color to normal when you press Ctrl-u.

Sub Macro1()
'
' Macro1 Macro
' Macro by Joseph S. Was
' Ctrl-s
'
ActiveCell.Select
Application.CutCopyMode = False
With Selection.Interior
.ColorIndex = 36
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
End Sub

Sub Macro2()
'
' Macro2 Macro
' Macro by Joseph S. Was
' Ctrl-u
'
ActiveCell.Select
Selection.Interior.ColorIndex = xlNone
End Sub

Re: Use Hot-key Macro

Thanks for the code Joe, I will fool around with it and see what I get!

Macro1 Macro Macro by Joseph S. Was Ctrl-s ActiveCell.Select Application.CutCopyMode = False With Selection.Interior .ColorIndex = 36 .Pattern = xlSolid .PatternColorIndex = xlAutomatic End With ActiveCell.Value = NewVal

Re: Kinda works but doesn't

Hi Mark,

I fooled around with it some more but now I found an even bigger problem. I need to get it so it only works on mouse clicks, the way it is now if I start moving the curser keys around it highlights all the cells. This could get messy.

Rob : I'm creating a vacation calendar for the HR department in excel to track all employees vacation requests. Everything is going well, EXCEPT! I'm having a problem getting one thing to work. I would like to be able to click on a day in the calendar and have the cell change to a different color, and if clicked again, change back to the original color. I'm using an invisible rectangle in a cell and assigned it a macro to do the single click color change. This is the macro I assigned the rectangle: ActiveCell.Value = NewVal

Re: Kinda works but doesn't

I ain't real happy with this, but it's the best I can do at my skill level. takes a double click to put in or take out a '1'. maybe someone else can improve upon it. delete the SelectionChange macro and try this:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Excel.Range, Cancel As Boolean)
If Not Intersect(Target, Range("ColorRange")) Is Nothing Then
If Target.Value = 1 Then
Target.Value = Null
Range("ColorRange").Range("a1").Select
Else
Target.Value = 1
Range("ColorRange").Range("a1").Select
End If
End If
End Sub

Good Luck

-Mark