title: Variables in Conditional Formatting Formula1
body: <p>I'm trying to use variables in the FormatCondition Formula1 property. The variables will be  cell references. However, I can't get the syntax right. The two bits I'm having trouble with in the code below are: <code>"=(C$3:J$10=""CM"")"</code> and <code>"=($C3:$J10=""RM"")"</code>.</p>
  
  <p>The aim of this is to highlight a column with CM in a certain cell, and to highlight a row with RM in a certain cell. The number of columns and rows will increase and decrease, hence the use of variables.</p>
  
  <p>Or if this isn't the right way or the best way, alternatives would be appreciated.</p>
  
  <p>The code is:</p>
  
  <pre><code>Private Sub Workbook_Open()
  Application.ScreenUpdating = False
  'Rows
  Dim iRowA As Integer, iRowB As Integer, iRowC As Integer
  Dim iRowDataStart As Integer, iRowLast As Integer
  'Columns
  Dim iColX As Integer, iColY As Integer, iColZ As Integer
  Dim iColDataStart As Integer, iColLast As Integer
  'Ranges
  Dim rAll As Range
  Dim rRowB As Range, rColY As Range
  Dim rRowMark As Range, rColMark As Range
  'String
  Dim sString As String
  'Assign values, normally these would be variable values, not assigned
  iRowA = 1: iRowB = 2: iRowC = 3
  iRowDataStart = 4: iRowLast = 10
  iColX = 1: iColY = 2: iColZ = 3
  iColDataStart = 4: iColLast = 10
  'Set ranges
  Set rAll = Range(Cells(iRowA, iColX), Cells(iRowLast, iColLast))
  Set rRowB = Range(Cells(iRowB, iColZ), Cells(iRowLast, iColLast))
  Set rColY = Range(Cells(iRowC, iColY), Cells(iRowLast, iColLast))
  Set rRowMark = Range(Cells(iRowC, iColZ), Cells(iRowLast, iColLast))
  Set rColMark = Range(Cells(iRowC, iColZ), Cells(iRowLast, iColLast))
  'Delete all CF currently in the worksheet
  With rAll
      .FormatConditions.Delete
  End With
  'Format column with Column Mark
  sString = "=(C$3:J$10=""CM"")"
  With rRowB
      .FormatConditions.Add _
          Type:=xlExpression, _
          Formula1:=sString
      .FormatConditions(.FormatConditions.Count).SetFirstPriority
      With .FormatConditions(1)
          .Interior.Color = RGB(196, 189, 151)
          .StopIfTrue = False
      End With
  End With
  'Format row with Row Mark
  sString = "=($C3:$J10=""RM"")"
  With rColY
      .FormatConditions.Add _
          Type:=xlExpression, _
          Formula1:=sString
      .FormatConditions(.FormatConditions.Count).SetFirstPriority
      With .FormatConditions(1)
          .Font.ColorIndex = 2
          .Interior.Color = RGB(127, 127, 127)
          .StopIfTrue = False
      End With
  End With
  Range("A1").Select
  Application.StatusBar = False
  Application.CutCopyMode = False
  End Sub
  </code></pre>