@
@Homeonthecouch
Okay I'm way out of my depth here as I have no idea about VBA but seeing as
I have started digging I want to know more.
I have looked at a few webpage's including
http://www.mvps.org/dmcritchie/excel/event.htm
The problem I have is this
I want to change the colours of cells depending on criteria (this bit I have
done)
I also want to change Cell A to insert a date and time (NOW) when I input
something in cell B ( I have also done this on a test sheet)
I cannot get the two to run on the same sheet?
What am I doing wrong?
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Range("F:F")) Is Nothing Then Exit Sub
With Target
Select Case (.Value)
Case Is = "Rob": .Interior.ColorIndex = 1: .Font.ColorIndex =
2: .Font.Bold = True
Case Is = "Jim": .Interior.ColorIndex = 8
Case Is = "Tom": .Interior.ColorIndex = 7
Case Is = "Bill": .Interior.ColorIndex = 3: .Font.ColorIndex =
1: .Font.Bold = True
Case Is = "Sue": .Interior.ColorIndex = 5
Case Else
.Interior.ColorIndex = 0
End Select
End With
If Target.Column <> 2 Then Exit Sub
If IsEmpty(Target(1)) Then Exit Sub '-- also rejects if entire row
cleared
If Target.Row = 1 Then Exit Sub
If IsEmpty(Target.Offset(0, -1)) Then
Target.Offset(0, -1) = Now
End If
End Sub
Any help in the right direction is appreciated as always.
Andrew
I have started digging I want to know more.
I have looked at a few webpage's including
http://www.mvps.org/dmcritchie/excel/event.htm
The problem I have is this
I want to change the colours of cells depending on criteria (this bit I have
done)
I also want to change Cell A to insert a date and time (NOW) when I input
something in cell B ( I have also done this on a test sheet)
I cannot get the two to run on the same sheet?
What am I doing wrong?
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Range("F:F")) Is Nothing Then Exit Sub
With Target
Select Case (.Value)
Case Is = "Rob": .Interior.ColorIndex = 1: .Font.ColorIndex =
2: .Font.Bold = True
Case Is = "Jim": .Interior.ColorIndex = 8
Case Is = "Tom": .Interior.ColorIndex = 7
Case Is = "Bill": .Interior.ColorIndex = 3: .Font.ColorIndex =
1: .Font.Bold = True
Case Is = "Sue": .Interior.ColorIndex = 5
Case Else
.Interior.ColorIndex = 0
End Select
End With
If Target.Column <> 2 Then Exit Sub
If IsEmpty(Target(1)) Then Exit Sub '-- also rejects if entire row
cleared
If Target.Row = 1 Then Exit Sub
If IsEmpty(Target.Offset(0, -1)) Then
Target.Offset(0, -1) = Now
End If
End Sub
Any help in the right direction is appreciated as always.
Andrew