2 change events on 1 worksheet

[selfteaching]

Hi
I have a worksheet that has two change events.
One works and this one doesn't

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("c138:c300")) Then
ActiveCell.Offset(0, 6).Range("A1").Select
End If
End Sub
I want the cursor to move over to Row I when C changes

Can I have 2 different change events or do I need to combine this with the other one?
If I have to combine it with the other one, I'll need help doing it

mike

[Kenneth Hobs]

No, combine the two...

[Paul_Hossler]

I'm a little confused

"Column I" or "Row 1"?

It seems like your current macro will already select I38 if C38 changes.

What else did you want to do?

[selfteaching]

Hi Ken,
I'm going to need help
here is the other one

Private Sub Worksheet_Change(ByVal Target As Range)
If Range("difference").Value = "MORE Than Last Lease" And Not Intersect(Target, Range("I1:I600")) Is Nothing Then
Application.EnableEvents = False
Dim a As Long
With Range("difference")
For a = 1 To 5
.Font.ColorIndex = 2
Application.Wait (Now + TimeValue("00:00:01"))
.Font.ColorIndex = 3
Application.Wait (Now + TimeValue("00:00:01"))
Next a
End With
Application.EnableEvents = True
End If
End Sub
I tried putting this after the last End If and before End Sub and also before If Range>>>>but I get and error message.

If Intersect(Target, Range("c138:c300")) Then
ActiveCell.Offset(0, 6).Range("A1").Select
'End If

What I'm doing is: if Range "difference" has the text "more than last lease", it will flash a few times after I enter a number in I row
Range"difference" has an If formula that puts in the text if a condition is met in a different cell
I want the cursor to move over 6 cells after I put in a number in C.


mike

[selfteaching]

Hi Paul
I didn't see your reply until after I submitted Ken's reply

I'm under the impression the "line" is horizontal and "row" is vertical
so C138 is Row C and line 138
If I'm wrong, sorry, and I will change any future questions. (which there will be many :()
What is the correct words?

I hope I explained what I'm trying to do in Ken's reply.

Thank yo for your input. As my ID says, I'm teaching myself using this forum and google.

mike

[Paul_Hossler]

1. No problem

2. 'Rows' are numbered, 'Columns' are lettered - so C138 is Row 138, Column C

3. I assume that "difference" is a named range

4. Something like this might get you started. Not sure I understood everything




Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim i As Long

If Not Intersect(Target, Range("c138:c300")) Is Nothing Then
Target.Offset(0, 6).Select


ElseIf Not Intersect(Target, Range("I1:I600")) Is Nothing Then

If Range("difference").Value = "MORE Than Last Lease" Then
Application.EnableEvents = False
With Range("difference")
For i = 1 To 5
.Font.ColorIndex = 2
Application.Wait (Now + TimeValue("00:00:01"))
.Font.ColorIndex = 3
Application.Wait (Now + TimeValue("00:00:01"))
Next i
End With
Application.EnableEvents = True
End If

End If

End Sub

[selfteaching]

Thanks Paul
It does what I want

I'm keeping track of my leased truck.
I enter the millage each time I get gas.
I did this for the last leased truck
I have a formula that compares the new millage with the last leased millage for the same number of days.
The range named "difference" is in view and will flash/blink about 3 times when I'm over millage from what I did on the last lease
The event code does this when I enter data in the I Column
I start by entering the date in A, B gets millage, C,D,E,F,G,and H have formula's for different things like average millage, number of days since last fill up, etc
Column I shows how many miles I can go till next fill up
I thought it would be nice to go to I after B directly

I can up with the event code to make the cell flash if it had a certain text in it by goggleing and adapting to my needs.
That is one way I'm learning Macros. I will also use this to help me write other similar code in the future.

When I have trouble, I come here :cool:

Thank you for your help :beerchug:

And you too, Ken :beerchug:

mike