MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Changing cell color by single click


Posted by Rob on June 22, 2001 7:08 AM

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:

Sub MarkDay()
' This routine marks the day of the week
' either on or off.


If ActiveCell.Value = "1" Then
NewVal = ""
ElseIf ActiveCell.Value = "" Then
NewVal = "1"
End If

ActiveCell.Value = NewVal

End Sub

I'm using the values 1 and "" as the days will be added to give a total vacation days taken. Each cell has a conditional format to check to see if the value is 1, if so make the cell blue if its blank leave it white. At the moment this works, it changes the active cell from blue to white when I click the mouse, the problem is that the active cell is not always the one I am clicking because clicking the rectangle does not make IT'S cell active. Is there a way to make the cell that contains the rectangle the active cell? I'm using the object ActiveCell in my code, Is there an object that references the cell from the actual clicked rectangle? like a MacroRefererCell or something. I hope this makes sense, I'm a newbie when it comes to coding, but i understand it when I look at it. Thanks.


Posted by mseyf on June 22, 2001 8:27 AM

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

Posted by Rob on June 22, 2001 11:55 AM

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!

Posted by Joe Was on June 22, 2001 11:56 AM

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

Posted by Rob on June 22, 2001 12:00 PM

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

Posted by Rob on June 22, 2001 1:22 PM

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

Posted by mseyf on June 22, 2001 2:24 PM

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