title: For Each Loop dependant options
body: <p>While working on a loop which works, I want to create a dependant choice. </p>
  
  <p>The user must select on Sheet2 whether a product will be shown on sheet1 or not by hiding the row. If he wants a product to be shown, he must fill in "a" in the Range("Checkmark"). See image below. </p>
  
  <p><a href="https://i.stack.imgur.com/btaJe.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/btaJe.png" alt="Image1"></a></p>
  
  <p>VBA will then search with a code which products have an "a" and take the named range of that cell and add an "1" after it so the named range on sheet1 will be hidden or unhidden. For example, if product A is selected, VBA will take Named range "ProductA" and make it into "ProductA1". Named range "ProductA1" will then be shown on sheet 1. See images below.</p>
  
  <p><a href="https://i.stack.imgur.com/iRjQU.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/iRjQU.png" alt="Image2"></a></p>
  
  <p><a href="https://i.stack.imgur.com/VacPw.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/VacPw.png" alt="Image3"></a></p>
  
  <p>The second choice is whether the product must be black or white. If chosen "black" then a certain named range on sheet 1 will be shown and "white" will be hidden. It could also be the other way around. The product itself contains some info named "Productinfo". This should be shown if an "a" has been filled in on sheet 2 under checkmark, if the product is not selected, all rows (Named range "ProductA1") should be hidden for that product on sheet 1. See images below.</p>
  
  <p><a href="https://i.stack.imgur.com/npzS7.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/npzS7.png" alt="Image4"></a></p>
  
  <p><a href="https://i.stack.imgur.com/fDUHK.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/fDUHK.png" alt="Image5"></a></p>
  
  <p>See all named ranges in image below.</p>
  
  <p><a href="https://i.stack.imgur.com/CT4yi.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/CT4yi.png" alt="Image6"></a></p>
  
  <p>So this is the code:</p>
  
  <pre><code>    Sub Choosingproduct()
  
  Application.ScreenUpdating = False
  Dim cell As Range
  For Each cell In Range("Checkmark") 'Where user puts in value "a" or not
  'Searching for the products that have an "a" in checkmark, then replacing the name and adding a "1" so VBA knows which range to hide or unhide on sheet 1
      Sheets("Sheet1").Range(Replace(cell.Name.Name, cell.Name.Name, cell.Name.Name &amp; "1")).EntireRow.Hidden = Not cell.Value = "a"
  
  
      Dim rCell As Range
      For Each rCell In Range("Productchoice")
      If cell.Value = "a" Then
          Sheets("Sheet1").Range(Replace(rCell.Name.Name, rCell.Name.Name, rCell.Name.Name &amp; "black")).EntireRow.Hidden = Not rCell.Value = "Black"
          Sheets("Sheet1").Range(Replace(rCell.Name.Name, rCell.Name.Name, rCell.Name.Name &amp; "white")).EntireRow.Hidden = Not rCell.Value = "White"
      Else
          Sheets("Sheet1").Range(Replace(rCell.Name.Name, rCell.Name.Name, rCell.Name.Name &amp; "black")).EntireRow.Hidden = Not rCell.Value = "Black"
          Sheets("Sheet1").Range(Replace(rCell.Name.Name, rCell.Name.Name, rCell.Name.Name &amp; "white")).EntireRow.Hidden = Not rCell.Value = "White"
      End If
  
      Next rCell
  
  Next cell
  
  Application.ScreenUpdating = True
  
  
  End Sub
  </code></pre>
  
  <p>How can I make VBA search for each separate component in the second loop? I get the run-time error 1004, Application-defined or object-defined error.</p>
  
  <p>UPDATE:</p>
  
  <p>I tried a different code and this seems to work:</p>
  
  <pre><code>Private Sub Worksheet_Change(ByVal Target As Range)
  
  Dim cell As Range
  Application.ScreenUpdating = False
  For Each cell In Range("Checkmark") 'Where user puts in value "a" or not
      Sheets("Sheet1").Range(Replace(cell.Name.Name, cell.Name.Name, cell.Name.Name &amp; "1")).EntireRow.Hidden = Not cell.Value = "a"
  
          If cell.Value = "a" Then
              If Sheets("Sheet2").Range(Replace(cell.Name.Name, cell.Name.Name, cell.Name.Name &amp; "choice")).Value = "Black" Then
                  Sheets("Sheet1").Range(Replace(cell.Name.Name, cell.Name.Name, cell.Name.Name &amp; "choiceblack")).EntireRow.Hidden = False
              Else
                  Sheets("Sheet1").Range(Replace(cell.Name.Name, cell.Name.Name, cell.Name.Name &amp; "choiceblack")).EntireRow.Hidden = True
              End If
          End If
  
          If cell.Value = "a" Then
              If Sheets("Sheet2").Range(Replace(cell.Name.Name, cell.Name.Name, cell.Name.Name &amp; "choice")).Value = "White" Then
                  Sheets("Sheet1").Range(Replace(cell.Name.Name, cell.Name.Name, cell.Name.Name &amp; "choicewhite")).EntireRow.Hidden = False
              Else
                  Sheets("Sheet1").Range(Replace(cell.Name.Name, cell.Name.Name, cell.Name.Name &amp; "choicewhite")).EntireRow.Hidden = True
              End If
          End If
  
  Next cell
  
  Application.ScreenUpdating = True
  
  End Sub
  </code></pre>