Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Changing cell color by clicking the cell
I have a vacation schedule for employees. As they turn in a vacation request,
I add it to the spreadsheet as pending. Once it is approved by management, I would like to click the cell and have it turn green and the letter 'A' appear as approved. If it is denied, the cell should be turned red and the letter 'D' inserted. The original input cell has a '1', '2', '3' (ist, 2nd, 3rd chocie) or a V as a non-choice vacation (usually a one day request. The pending status is always normal white cell shading. Is there any way of cycling through the cell color choices....green to red...as I click on the cell ? I'm sure I would have to do each cell separatly (2 week vacation, for example) but that would still be easier than highlighting and re-typing every cell. I did notice a 2007 posting for changing from one color to the next, but not sure about cycling through with multiple choices. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Changing cell color by clicking the cell
This code works off of right click. If you right click in column A row 3 or
more then it changes the colour. Right click the sheet tab you want and select view code. Paste this into the code window... Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean) If Target.Count 1 Then Exit Sub If Target.Column = 1 And Target.Row = 3 Then Cancel = True With Target.Interior Select Case Target.Interior.ColorIndex Case xlNone .ColorIndex = 3 Case 3 .ColorIndex = 6 Case 6 .ColorIndex = 4 Case 4 .ColorIndex = xlNone End Select End With End If End Sub -- HTH... Jim Thomlinson "HarveyM." wrote: I have a vacation schedule for employees. As they turn in a vacation request, I add it to the spreadsheet as pending. Once it is approved by management, I would like to click the cell and have it turn green and the letter 'A' appear as approved. If it is denied, the cell should be turned red and the letter 'D' inserted. The original input cell has a '1', '2', '3' (ist, 2nd, 3rd chocie) or a V as a non-choice vacation (usually a one day request. The pending status is always normal white cell shading. Is there any way of cycling through the cell color choices....green to red...as I click on the cell ? I'm sure I would have to do each cell separatly (2 week vacation, for example) but that would still be easier than highlighting and re-typing every cell. I did notice a 2007 posting for changing from one color to the next, but not sure about cycling through with multiple choices. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Changing cell color by clicking the cell
migod Jim, that was very easy..Thanks ! I just added an ActiveCell = (letter) to each line to get the (A)pproved, (D)enied, (P)ending status to show up Thanks again "Jim Thomlinson" wrote: This code works off of right click. If you right click in column A row 3 or more then it changes the colour. Right click the sheet tab you want and select view code. Paste this into the code window... Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean) If Target.Count 1 Then Exit Sub If Target.Column = 1 And Target.Row = 3 Then Cancel = True With Target.Interior Select Case Target.Interior.ColorIndex Case xlNone .ColorIndex = 3 Case 3 .ColorIndex = 6 Case 6 .ColorIndex = 4 Case 4 .ColorIndex = xlNone End Select End With End If End Sub -- HTH... Jim Thomlinson "HarveyM." wrote: I have a vacation schedule for employees. As they turn in a vacation request, I add it to the spreadsheet as pending. Once it is approved by management, I would like to click the cell and have it turn green and the letter 'A' appear as approved. If it is denied, the cell should be turned red and the letter 'D' inserted. The original input cell has a '1', '2', '3' (ist, 2nd, 3rd chocie) or a V as a non-choice vacation (usually a one day request. The pending status is always normal white cell shading. Is there any way of cycling through the cell color choices....green to red...as I click on the cell ? I'm sure I would have to do each cell separatly (2 week vacation, for example) but that would still be easier than highlighting and re-typing every cell. I did notice a 2007 posting for changing from one color to the next, but not sure about cycling through with multiple choices. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Changing Cell Color by clicking on a cell | Excel Programming | |||
Changing cell text color based on cell number | Excel Programming | |||
Cell colors or text color changing when date in cell gets closer. | Excel Worksheet Functions | |||
Code for making a cell chage color by dbl clicking, with either a | Excel Programming | |||
changing cell value by double clicking it with out vba/macros | Excel Programming |