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. |
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. |
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. |
All times are GMT +1. The time now is 04:43 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com