View Full Version : [SOLVED:] 2 change events on 1 worksheet
selfteaching
01-14-2019, 04:35 PM
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
01-14-2019, 05:04 PM
No, combine the two...
Paul_Hossler
01-14-2019, 05:35 PM
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
01-14-2019, 05:58 PM
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
01-14-2019, 06:06 PM
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
01-14-2019, 07:44 PM
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
01-14-2019, 08:13 PM
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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.