Run diffrent macro if cel have values Yes on NO

siprian · May 6th 2022

Hi,

I have a query and one macro that run on Sheet1 to copy values to Sheet2.

I'm trying to create a macro in Sheet2 that will run if the value of the cell is YES or NO

I have created 4 macros for Sheet 2 and named them Macro1, Macro2 ,Macro3, Macro4

If cell A7 has value YES and a8 , A9 A10 have value NO, then Macro1 should run.

If cell A7 and A8 have value YES and A9 and A10 has value NO, then Macro2 should run

If cell A7, A8 and A9 have value YES and A10 has value NO, then Macro3 should run.

If cell A7, A8 , A9 and A10 have value YES then Macro4 should run.

Thank you!

**royUK** · May 7th 2022

Try this

Code

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Target.Address = "$A$7" Then Exit Sub
    Select Case UCase(Target.Value)
        Case "YES"
            Debug.Print Target.Value
            Debug.Print Application.WorksheetFunction.CountIfs(Range("A8:A10"), "NO")
            If Application.WorksheetFunction.CountIfs(Range("A8:A10"), "NO") = 3 Then Macro1
            If Application.WorksheetFunction.CountIfs(Range("A8:A9"), "NO") = 2 Then Macro2
            If Application.WorksheetFunction.CountIfs(Range("A7:A9"), "YES") = 2 And UCase(Range("A10")) = "NO" Then Macro3
            If Application.WorksheetFunction.CountIfs(Range("A7:A10"), "YES") = 4 Then Macro4
    End Select
End Sub

Display More

This is worksheet event code, read this if you aren't sure where to paste it.

Where to put the code

Run diffrent macro if cel have values Yes on NO

Participate now!

Share

Similar Threads

pulling cell values from other workbooks (listed paths)

INSERT Number of Rows based on Cell Value and populate rows below with initial value

VBA Loop to create Excel Sheet, save excel sheet to specific folder on computer and generate Outlook 365 Email with attached excel sheet.

Move row to another sheet based on text selected in cell

next for each copy data based on criteria

Tags

Users Viewing This Thread