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.

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.

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.