title: How can I use VBA to format Symbols / Icons into cells without using conditional formatting
body: <p>I am using VBA code to place  conditional formatting to cover values in a large table, I use 2 formulae per cell to determine which of the 3 symbols to use.  I need to check the value of each cell with a different cell depending on the column and therefore as far as I understamd, I have to place my conditional formatting rule on each cell individually to ensure the formula is correct in each.  This is because conditional formatting cannot take relative addresses, you have to give it the exact address of each cell ... correct?</p>
  
  <p>The large number of conditional formatting instances is slowing my computer to a huge extent.</p>
  
  <p>Is it possible to place symbols used by conditional formatting, into a cell, without using conditional formatting? </p>
  
  <p>Perhaps somewhat like an image, but whilst retaining the cell value underneath, as can be done using conditional formatting.</p>
  
  <p>Below I have given the code I use to put the conditional formatting in place. Any help is very much appreciated!!</p>
  
  <pre><code>    Dim AIs As Range
      Dim rng As Range
      Dim cl As Range
  
      Set AIs = ActiveSheet.Range("Table")
      For Each cl In AIs.Columns
          For Each rng In cl.Cells
  
          rng.FormatConditions.AddIconSetCondition
          rng.FormatConditions(rng.FormatConditions.Count).SetFirstPriority
          With rng.FormatConditions(1)
              .ReverseOrder = False
              .ShowIconOnly = True
              .IconSet = ActiveWorkbook.IconSets(xl3Symbols2)
          End With
  
           With rng.FormatConditions(1).IconCriteria(1)
              .Icon = xlIconYellowExclamationSymbol
          End With
          With rng.FormatConditions(1).IconCriteria(2)
              .Icon = xlIconRedCross
  
              .Type = xlConditionValueFormula
              .Value = "=IF(VALUE(LEFT(" &amp; rng.Parent.Cells(5, rng.Column).Address &amp; _
                    ";1)=0;1;6)"
  
              .Operator = 7
          End With
          With rng.FormatConditions(1).IconCriteria(3)
              .Icon = xlIconGreenCheck
  
              .Type = xlConditionValueFormula
              .Value = "=IF(VALUE(LEFT(" &amp; rng.Address &amp; ";1))&lt;=VALUE(LEFT(" &amp; _
                    rng.Parent.Cells(5, rng.Column).Address &amp; ";1));1;6)"
  
              .Operator = 7
          End With
          Next rng
      Next cl
  </code></pre>