Event Macro :)

[@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

 

[Dave Peterson]

Your code checks to see if you changed more than one cell (right at the top).
If you did change more than one cell, the event runs and exits after that first
line.

The second line checks to see where you made the change. If it's not in column
F, then you leave the event. You could look for column B in that same line:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Range("b:b,F:F")) Is Nothing Then Exit Sub

Select Case Target.Column
Case Is = Me.Range("F1").Column
With Target
Select Case LCase(.Value)
Case Is = LCase("Rob")
.Interior.ColorIndex = 1
.Font.ColorIndex = 2
.Font.Bold = True
Case Is = LCase("Jim")
.Interior.ColorIndex = 8
Case Is = LCase("Tom")
.Interior.ColorIndex = 7
Case Is = LCase("Bill")
.Interior.ColorIndex = 3
.Font.ColorIndex = 1
.Font.Bold = True
Case Is = LCase("Sue")
.Interior.ColorIndex = 5
Case Else
.Interior.ColorIndex = 0
End Select
End With
Case Is = Me.Range("b1").Column
With Target
If .Row = 1 Then
'do nothing
Else
If IsEmpty(.Cells) Then
'do nothing
Else
'don't fire again!
Application.EnableEvents = False
With .Offset(0, -1)
.NumberFormat = "mm/dd/yyyy hh:mm:ss"
.Value = Now
End With
Application.EnableEvents = True
End If
End If
End With
Case Else
'do nothing
End Select

End Sub

 

[@Homeonthecouch]

That's Magic....well it's not but when something you don't understand
correctly works then.....well you get the idea.

Now if I wanted the cells adjacent to F:F in column E:E to change colour the
same way they are doing in F:F could I add an "and" statement and use the
offset again?

Many thanks for the help already supplied.

Andrew

 

[Dave Peterson]

Do you want to base the color on E by the change in column F?

If yes, then instead of lines like this:
..Interior.ColorIndex = 1
you'd use:
..offset(0,-1).resize(1,2).Interior.ColorIndex = 1
(Move to the left one column (column E), but resize the area to change to be 2
columns (E:F).

You'd have to get all those colorindices and bolds the same way.

 

[@Homeonthecouch]

Marvellous that works a treat

Thank you

Andrew