Excel Macro: Run Macro Based on a cell value

[rorymcc]

Hi I'm trying to run a macro based on another cell value. I simply want to hide rows based on a dropdown value from another cell. Is this possible. I have working code that is hiding rows, but it is not being triggered when the value is selected I have to manually run the macro. Could someone help me?

Code:

Private Sub worksheet_change(ByVal target As Range)
Set target = Range("B1")
If target.value = "Delete" Then
Call Macro1
End If
End Sub


Sub Macro1

If Range("B1").Value="Delete" Then
Rows("3:4").EntireRow.Hidden = True .......etc


The sub macro1 is working but not being triggered by the private sub on the worksheet

 

[Joe4]

Do you know why S_Wish code would provide an error using the Case statement?

Yes.
It needs to be

Case Else

not

Else

 

[rorymcc]

I'm going to be a pain on this forum I think, but looking to learn VBA and improve.

I've just changed something that means a value of the cell is as a result of a formula. However the macro doesn't run from the result of a formula. However if a type in the cell the result it does work. How can i change it so it runs from a result of a formula?

 

[Joe4]

Worksheet_Change procedures fire on the manual change of a cell. If the change is the result of a formula change, it will not fire a Worksheet_Change procedure, based on that cell.
There is a Worksheet_Calculate procedure which fires when a formula calculation occurs, but you cannot limit it to a particular cell, meaning it fires whenever any calculation on the sheet is updated (often overkill).

What I often like to do, if possible, is look at the formula in B1, and have the Worksheet_Change procedure fire based on changes to the cells that the formula calls.
So what is the formula?

 

[rorymcc]

Hi Joe,

Formula is =IF(COUNTIF(C8:C12,"Yes")>=1,"Yes","False")

So want B1 to run the Macro if the result is Yes and unhide the rows if anything else/no.

 

[Joe4]

Try this:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Range("C8:C12"), Target) Is Nothing Then
        If Range("B1") = "Yes" Then
            Rows("3:4").EntireRow.Hidden = True
        Else
            Rows("3:4").EntireRow.Hidden = False
        End If
    End If
End Sub

The key line is this here:

If Not Intersect(Range("C8:C12"), Target) Is Nothing Then

What it essentially says is "Is the cell being updated in the range C8:C12"?

 

[rorymcc]

You're a genius

 

[Joe4]

You're a genius

I don't know if I would go that far! There are users here who blow me away with some of the stuff they do.
Let's just say I am "very experienced"!

 

[rorymcc]

does anybody know how i would run two macros within one worksheet change. My code ive tried so far hasn't worked for the second macro but is running the first one:

Private Sub Worksheet_Change(ByVal Target As Range)
My_Sub_A Target
My_Sub_B Target


End Sub


Public Sub My_Sub_A(ByVal Target As Range)
If Not Intersect(Range("C16:C21"), Target) Is Nothing Then
If Range("C22") = "Yes" Then
Rows("23:25").EntireRow.Hidden = True
Else
Rows("23:25").EntireRow.Hidden = False
End If
End If
End Sub

Public Sub My_Sub_B(ByVal Target As Range)
If Not Intersect(Range("F2:F3"), Target) Is Nothing Then
If Range("F4") = "Yes" Then
Rows("27:28").EntireRow.Hidden = True
Else
Rows("27:28").EntireRow.Hidden = False
End If
End If
End Sub

 

[My Aswer Is This]

You would do it like this:
But you had:
C16:C21
But then referred to C22 which is outside the range
You did this in both codes. I changed them both.

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Range("C16:C22"), Target) Is Nothing Then
If Range("C22") = "Yes" Then
Rows("23:25").EntireRow.Hidden = True
Else
Rows("23:25").EntireRow.Hidden = False
End If
End If
If Not Intersect(Range("F2:F4"), Target) Is Nothing Then
If Range("F4") = "Yes" Then
Rows("27:28").EntireRow.Hidden = True
Else
Rows("27:28").EntireRow.Hidden = False
End If
End If
End Sub

 

[My Aswer Is This]

I would think both codes should be like this:
See code change marked in red.

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Range("[COLOR=#ff0000]C22[/COLOR]"), Target) Is Nothing Then
If Range("C22") = "Yes" Then
Rows("23:25").EntireRow.Hidden = True
Else
Rows("23:25").EntireRow.Hidden = False
End If
End If
If Not Intersect(Range("[COLOR=#ff0000]F4[/COLOR]"), Target) Is Nothing Then
If Range("F4") = "Yes" Then
Rows("27:28").EntireRow.Hidden = True
Else
Rows("27:28").EntireRow.Hidden = False
End If
End If
End Sub