AboutAidan Heritage Expertise I have provided first hand support since `95 for Microsoft Office majoring in Word and Excel - support for
all versions from 2 onwards
Experience My background is in the insurance industry and call centre areas, but have been called upon to provide many varied solutions.
Education/Credentials I'm educated to UK A level standard, but as I left school some 30 years ago that is rather irrelevent - university of life has provided more of a background!
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)
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.
About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here
