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:
' This routine marks the day of the week
' either on or off.
If ActiveCell.Value = "1" Then
NewVal = ""
ElseIf ActiveCell.Value = "" Then
NewVal = "1"
ActiveCell.Value = NewVal
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.