Combining 2 Worksheet_Change events

[orion2323]

Hello again!

I could use a little help with the following

How can I go about combining these 2 events: (If possible)

#1 takes a formula result from one cell, and pastes the value to a different cell
#2 will automatically print when a cell value matches the trigger

#1

Option Explicit
 
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("B5")) Is Nothing Then
    If InStr(1, Target, "COLOR", vbTextCompare) Then
        Target.Offset(0, 1) = "COLOR"
    End If
End If
End Sub

#2

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim targCell As Range
    Set targCell = Worksheets(1).Range("B2")
 
    If Not Application.Intersect(Target, targCell) Is Nothing Then
        If targCell.Value = COLOR Then
            Worksheets(1).PrintOut
        End If
    End If
End Sub

Thank you All for your time!

 

[Joe4]

Try this:

Private Sub Worksheet_Change(ByVal Target As Range)

    If Not Intersect(Target, Range("B5")) Is Nothing Then
        If InStr(1, Target, "COLOR", vbTextCompare) Then
            Target.Offset(0, 1) = "COLOR"
        End If
    End If
    
    Dim targCell As Range
    Set targCell = Worksheets(1).Range("B2")

    If Not Application.Intersect(Target, targCell) Is Nothing Then
        If targCell.Value = Color Then
            Worksheets(1).PrintOut
        End If
    End If
    
End Sub

 

[orion2323]

Thank you for your time and reply
I tried the code above and it didn't work for my needs (or it didn't execute the way I thought it would)

What I really need, if possible, is to have excel print automatically (which it does based on the macro below)
However, this macro is locked to a specific value and a specific cell "B2" and the word Color

Is there a way to have the Cell location move down to the next natural location, B3, then B4, B6 and so on (in the macro), so that when I type Color in cell B2 it prints as it does now, then I will move to B3 and type the same, prompting the macro to print again?

As an alternative, I have merged 100 cells under Column B (a "big" B2) and always retype the same value so the Macro works

Thank you again for your time


#2

Private Sub Worksheet_Change(ByVal Target As Range)
Dim targCell As Range
Set targCell = Worksheets(1).Range("B2")

If Not Application.Intersect(Target, targCell) Is Nothing Then
If targCell.Value = COLOR Then
Worksheets(1).PrintOut
End If
End If
End Sub

 

[Joe4]

Are you sure that you copied your code exactly as you have it, and it is working properly?
Because this line looks incorrect in number 2:

[COLOR=#333333]If targCell.Value = COLOR Then[/COLOR]

I think it should be:

[COLOR=#333333]If targCell.Value = "COLOR" Then[/COLOR]

Is there a way to have the Cell location move down to the next natural location, B3, then B4, B6 and so on

You need to explain to us exactly how to determine exactly where the "next natural location" is, as it appears to skip some cells.

 

[orion2323]

Sorry, above was a typo, cells will always be consecutive, B2 through B50 (possibly B100)

And yes, it works the way it is

Private Sub Worksheet_Change(ByVal Target As Range)
Dim targCell As Range
Set targCell = Worksheets(1).Range("B2")


If Not Application.Intersect(Target, targCell) Is Nothing Then
If targCell.Value = 2 Then
Worksheets(1).PrintOut
End If
End If
End Sub

 

[Joe4]

If you want it to work for any cell in the range B2:B50, then change this line:

[COLOR=#333333]Set targCell = Worksheets(1).Range("B2")

to this:
[/COLOR]

[COLOR=#333333]Set targCell = Worksheets(1).Range("B2:B50")[/COLOR]

 

[orion2323]

Thank you, again
I will try your solution once back in the office
FYI, I recall trying a similar approach and it didn't work at the time...

Basically I type "2" in B2 and Excel prints a printable area, I then move to B3 and repeat, printing immediately, over and over again

 

[Joe4]

FYI, I recall trying a similar approach and it didn't work at the time...

Basically I type "2" in B2 and Excel prints a printable area, I then move to B3 and repeat, printing immediately, over and over again

Yep, it should work that way. I use this technique all the time.
I have no idea how you may have tried it previously, but I suspect that maybe it wasn't done quite right.

 

[orion2323]

Finally back...Happy new year to All

Just tried and received the following error:

Runtime error "13"
Type mismatch

Sub jumpnext()
Range("B" & ActiveCell.Row + 1).Select
End Sub


Private Sub Worksheet_Change(ByVal Target As Range)
Dim targCell As Range
Set targCell = Worksheets(1).Range("C2:C100")


If Not Application.Intersect(Target, targCell) Is Nothing Then
If targCell.Value = 2 Then
Worksheets(1).PrintOut
End If
End If
End Sub

 

[Joe4]

I think maybe in place of this:

[COLOR=#333333]If targCell.Value = 2 Then[/COLOR]

you want this:

If Target.Value =  2 Then