Need help with change event

[Russk68]

Hi all,

I have several different macros in a event change. I would like to have different macros run, based on certain cells that change.

Example:

This code I want to run when any cell is changed:

Private Sub Worksheet_Change(ByVal Target As Range)
Worksheets("Distro").Calculate

And this code to run only when cell F1 is changed:

If Range("GF3").Value = "2" Then 'Distro1x12
Rows("13:52").EntireRow.Hidden = True
ElseIf Range("gf3").Value = "6" Then
Rows("13:52").EntireRow.Hidden = False
End If

And this code to run only when cell F54 is changed:

End If
If Range("GF56").Value = "2" Then 'Distro2x12
Rows("66:105").EntireRow.Hidden = True
ElseIf Range("gf56").Value = "6" Then
Rows("66:105").EntireRow.Hidden = False

The combined code is below.


Private Sub Worksheet_Change(ByVal Target As Range)
Worksheets("Distro").Calculate
If Range("GF3").Value = "2" Then
Rows("13:52").EntireRow.Hidden = True
ElseIf Range("gf3").Value = "6" Then
Rows("13:52").EntireRow.Hidden = False
End If
If Range("GF56").Value = "2" Then
Rows("66:105").EntireRow.Hidden = True
ElseIf Range("gf56").Value = "6" Then
Rows("66:105").EntireRow.Hidden = False
End If

End Sub

Thank you!

Russ

 

[mrshl9898]

Hi,

Try this:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    If Not Intersect(Target, Target.Worksheet.Range("F1")) Is Nothing Then
        If Range("GF3").Value = "2" Then 'Distro1x12
        Rows("13:52").EntireRow.Hidden = True
        ElseIf Range("gf3").Value = "6" Then
        Rows("13:52").EntireRow.Hidden = False
        End If
    ElseIf Not Intersect(Target, Target.Worksheet.Range("F54")) Is Nothing Then
        If Range("GF56").Value = "2" Then
        Rows("66:105").EntireRow.Hidden = True
        ElseIf Range("gf56").Value = "6" Then
        Rows("66:105").EntireRow.Hidden = False
        End If
    End If
[COLOR=#333333]Worksheets("Distro").Calculate[/COLOR]
End Sub

 

[DanteAmor]

Try this

Private Sub Worksheet_Change(ByVal Target As Range)
    
    'This code I want to run when any cell is changed:
    Worksheets("Distro").Calculate
    
    'And this code to run only when cell F1 is changed:
    If Not Intersect(Target, Range("F1")) Is Nothing Then
        If Target.Count = 1 Then
            If Range("GF3").Value = "2" Then
                Rows("13:52").EntireRow.Hidden = True
            ElseIf Range("gf3").Value = "6" Then
                Rows("13:52").EntireRow.Hidden = False
            End If
        End If
    End If
    
    'And this code to run only when cell F54 is changed:
    If Not Intersect(Target, Range("F54")) Is Nothing Then
        If Target.Count = 1 Then
            If Range("GF56").Value = "2" Then
                Rows("66:105").EntireRow.Hidden = True
            ElseIf Range("gf56").Value = "6" Then
                Rows("66:105").EntireRow.Hidden = False
            End If
        End If
    End If


End Sub

 

[Russk68]

That works!
Thank you very much Dante

 

[DanteAmor]

You're welcome & thanks for the feedback.