title: VBA Excel - Defining a workbook and worksheet variable value from cell
body: <p>I have searched for this all day but have not been able to work it out, so hopefully y'all can help :)</p>
  
  <p><strong>Objective:</strong>
  For a specified sheet in a separate workbook (from which this code is ran), for a list of row numbers in column O, the script should run and highlight all of the corresponding row numbers yellow.</p>
  
  <p><strong>Context:</strong>
  I'm an auditor so we often have to pick random samples from Excel workbooks, with 1000s of rows and then highlight a random selection manually. I'd like to automate this procedure.</p>
  
  <p><strong>Specific problem:</strong>
  Have my loop working and highlighting yellow, but cannot seem to get the variables for the 'target location' for which I want the code to run to work. When I hard-code "Sheet2", then it works on Sheet 2. But even if I try just using a cell value as a variable, and in that cell putting "Sheet2", it just won't work. I've tried many more ways than what I've included below, but I just tidied up the current code and pasted below.</p>
  
  <p><strong>Details for end deliverable</strong> (all following inputs are in a single-sheet WB, sheet name is called 'Random':
   - User enters a folder location in cell A5
   - User enters a folder location in cell A6
   - User enters a sheet name in cell A7
   - User pastes list of numbers for highlighting into column O
   - The user should then hit 'Highlight samples' button for which the code should then highlight all the row numbers in column O yellow for the target worksheet in target workbook.</p>
  
  <p><strong>Code</strong> (modified from another post):</p>
  
  <pre><code>Sub sample()
      Dim i As Long, workbk As Workbook, worksh As Worksheet  
  
      On Error GoTo Whoa
  
      Application.ScreenUpdating = False
  
  
      Set worksh = Sheets("Sheet2") 'Hard-coded, this works
  
      'Set worksh = Range("B6").Value 'Doesn't work
      'Set worksh = Sheets(Range("pickupsheetname")) 'Doesn't work
  
  
      With Sheets("RandomList")
          For i = 1 To 30
              If Not Len(Trim(.Range("O" &amp; i).Value)) = 0 And _
              IsNumeric(.Range("O" &amp; i).Value) Then _
                   worksh.Rows(.Range("O" &amp; i).Value).Interior.ColorIndex = 6 '&lt;~~ Yellow
          Next i
      End With
  
  LetsContinue:
      Application.ScreenUpdating = True
      Exit Sub
  Whoa:
      MsgBox Err.Description
      Resume LetsContinue
  End Sub
  </code></pre>