title: Colour shapes based on value in array
body: <p>I am trying to automate colouring shapes in excel dependent on values in a table. The approach I have taken is to read the table into an array, then I want to use this to determine how to colour the shapes. The problem I'm having is when I use the array to select a shape, it would appear that I get an array index number, not the actual value.</p>
  
  <p>I would greatly appriciate a pointer in the right direction as I have spent a few hours trying to find a soulution without luck.</p>
  
  <p>Table data:</p>
  
  <pre><code>Shape   Value
  AB      900
  DD      99
  DG      647
  EH      513
  FK      191
  G       446
  HS      369
  IV      259
  KA      601
  KW      351
  KY      23
  ML      509
  PA      987
  PH      167
  TD      325
  ZE      873
  </code></pre>
  
  <p>VBA Code:</p>
  
  <pre><code>Public i As Variant
  Public j As Integer
  
  Function mkArray()
  
      Dim areaArr As Variant
      areaArr = Range("I1:J16").Value
  
      Sheets("Sheet1").Select
      For i = 1 To UBound(areaArr, 1)
          For j = 1 To UBound(areaArr, 2)
              Debug.Print areaArr(i, j)
          Next j
              Call colourShapes
      Next i
  End Function
  
  Sub colourShapes()
  
      If j &gt;= 500 Then
          Call formatGreen
      Else
          Call formatRed
      End If
  End Sub
  
  Sub formatGreen()
  
      With ActiveSheet
          .Shapes(i).Fill.ForeColor.SchemeColor = 11
      End With
  End Sub
  
  Sub formatRed()
  
      With ActiveSheet
          .Shapes(i).Fill.ForeColor.SchemeColor = 2
      End With
  End Sub
  </code></pre>
  
  <p>Many thanks.</p>