none
If cell contains xxx then xxx RRS feed

  • Question

  • Hi,

    I need a simple If-then-else statement which will do this:

    If cell A1 contains the text "apple", "banana" or "orange" then print sheet1 and sheet2

    Else if cell A1 contains text "watermelon", "pineapple" or "rockmelon" then print sheet 3 and sheet 4

    Else if cell A1 contains text "mango" then print sheet 5.

    Thanks in advance!

    Tuesday, May 23, 2017 1:49 AM

All replies

  • Hi,

    Using "Select case" is better than using "If else".






    Private Sub Worksheet_Change(ByVal Target As Range)
      If Intersect(Target, Range("A1")) Is Nothing Then
        Exit Sub
      Else
        Call prc_SelectCase
      End If
    End Sub
    ' ---
    Private Sub prc_SelectCase()
      Select Case Range("A1").Value
        Case Is = "apple", "banana", "orange"
          MsgBox "Print sheet1 and sheet2"
        Case Is = "watermelon", "pineapple", "rockmelon"
          MsgBox "Print sheet 3 and sheet 4"
        Case Is = "mango"
          MsgBox "Print sheet 5"
      End Select
    End Sub

    [note]

      1. range A3:A9  is named "rng_Fruits"
          
      2. A1 is a combobox (Data Validation - List)
          
    _______________
    Ashidacchi
    • Edited by Ashidacchi Tuesday, May 23, 2017 3:44 AM
    Tuesday, May 23, 2017 3:44 AM