Expert: Aidan Heritage - 7/10/2008

Question
Hey Aidan

I am currently working on this code where I will select a drop down menu in a cell and make a selection which triggers a format change in the cell, and also to automatically update the cell next to it with the date the change was made.  I would like the code to incorporate a condition where if the person chooses the same selection that was already in the cell, it will not update the date cell.  Here is what I have so far...

Private Sub Worksheet_Change(ByVal Target As Range)
  
  '******************
  'Color Change Code
  '******************
  
  On Error Resume Next
  If Target.Validation.Type <> 0 Then
      If Err.Number <> 0 Then Exit Sub
      Select Case Target.Value
          Case ""
               Target.Interior.ColorIndex = 2
               Target.Borders.LineStyle = 1
               Target.Offset(-1, -1).Value = ""
               
          Case "Not Started"
               Target.Interior.ColorIndex = 2
               Target.Borders.LineStyle = 1
               Target.Font.Color = vbBlack
               Target.Font.FontStyle = Default
               Target.Offset(-1, -1).Value = Now
           
          Case "In-Prog"
              Target.Interior.ColorIndex = 34
              Target.Borders.LineStyle = 1
              Target.Font.Color = vbBlack
              Target.Font.FontStyle = Default
              Target.Offset(-1, -1).Value = Now
          
          Case "Re-Run"
              Target.Interior.ColorIndex = 35
              Target.Borders.LineStyle = 1
              Target.Font.Color = vbBlack
              Target.Font.FontStyle = Default
              Target.Offset(-1, -1).Value = Now
          
          Case "Completed"
              Target.Interior.ColorIndex = 4
              Target.Borders.LineStyle = 1
              Target.Font.FontStyle = "Bold"
              Target.Font.Color = vbBlack
              Target.Offset(-1, -1).Value = Now
                         
          Case "Pass"
              Target.Interior.ColorIndex = 4
              Target.Borders.LineStyle = 1
              Target.Font.Color = vbBlack
              Target.Font.FontStyle = Default
              Target.Offset(-1, -1).Value = Now
          
          Case "Partial Block"
              Target.Interior.ColorIndex = 45
              Target.Borders.LineStyle = 1
              Target.Font.FontStyle = "Bold"
              Target.Font.Color = vbWhite
              Target.Offset(-1, -1).Value = Now
          
          Case "Block"
              Target.Interior.ColorIndex = 45
              Target.Borders.LineStyle = 1
              Target.Font.Color = vbRed
              Target.Font.FontStyle = "Bold"
              Target.Offset(-1, -1).Value = Now
          
          Case "Fail"
              Target.Interior.ColorIndex = 3
              Target.Borders.LineStyle = 1
              Target.Font.Color = vbWhite
              Target.Font.FontStyle = "Bold"
              Target.Offset(-1, -1).Value = Now
        Case Else
      End Select
  End If
End Sub

Thanks in advance for any help you can give me
Nick

Answer
This was interesting research - I first found a suggestion of having a very hidden sheet which stored all the old values (http://www.tanguay.info/web/codeExample.php?id=872 ) but I think I've managed a neater solution, which is to use two events

Dim oldVal As Variant
Private Sub Worksheet_Change(ByVal Target As Range)
MsgBox Target.Value
MsgBox oldVal
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
oldVal = Target.Value
End Sub


I've set this up with message boxes so you can see what is happening, but basically the OldVal is stored with the selectionchange event which is called first, then the change event takes over - so you now know both the old and new values and can compare them.