VBA to run macro when multiple conditions met

[judwall]

I'm trying to write a VBA to run a macro when the following happens:

In Sheet1, a product # will be entered into cell B2. On Sheet2 I have formulas set up to read the product # and populate 5 different cells with numbers accordingly, using vlookup. I want my macro to run when the product # is entered but ONLY if all 5 cells (range=E4:I4) in Sheet2 have numbers in them,(versus the #N/A error).

How do I do this?

 

[JoeMo]

This is sheet code for Sheet1, install it following the steps below.
To install the code:
1. Right-click the worksheet tab you want to apply it to and choose 'View Code'. This will open the VBE window.
2. Copy the code below from your browser window and paste it into the white space in the VBE window.
3. Close the VBE window and Save the workbook. If you are using Excel 2007 or a later version do a SaveAs and save it as a macro-enabled workbook (.xlsm file extension).
4. Make sure you have enabled macros whenever you open the file or the code will not run.

Be sure to replace "myMacro" in the code below with the name of the macro you want to run if none of the 5 cells on Sheet2 displays an error.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim R1 As Range, R2 As Range
If Not Intersect(Range("B2"), Target) Is Nothing Then
    Set R1 = Sheets("Sheet2").Range("E4:I4")
    On Error Resume Next
    Set R2 = R1.SpecialCells(xlCellTypeFormulas, xlErrors)
    On Error GoTo 0
    If R2 Is Nothing Then Call myMacro  'put your macro name here
End If
End Sub

 

[JLGWhiz]

This code will go in sheet 1 code module. Right click the sheet name tab, then click 'View Code' and paste the code into the VB editor window when it opens. The workbook should be saved as a macro enabled workbook to preserve the code.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
Dim i As Long
Application.EnableEvents = False
If Not Intersect(Target, Range("B2")) Is Nothing Then
    For i = 5 To 9
        If Not IsNumeric(Sheets("Sheet2").Cells(4, i).Value) Then
            MsgBox "Required Cells Not Properly Populated"
            Application.EnableEvents = True
            Exit Sub
        End If
    Next
    'Other code here to do stuff
End If
Application.EnableEvents = True
End Sub

Checks for numeric values.

 

[judwall]

This worked perfectly! Thank you!!

 

[JoeMo]

This worked perfectly! Thank you!!

What does "This" refer to?

 

[JLGWhiz]

What does "This" refer to?

Must be yours JoeMo, I did not get any likes recorded. (g)

 

[judwall]

What does "This" refer to?

It refers to your suggestion. Didn't see JLG's before I replied

 

[JoeMo]

It refers to your suggestion. Didn't see JLG's before I replied

Glad we could help.