title: Coloring of a referenced cell (Excel, VBA)
body: <p>If I have a column of referenced numbers and I would like to color the referenced cell automatically after right clicking, how can I refer to the reference cell in VBA. In the exmaple I have the referenced values in column [O]. At [O4] I have the value [=$G$12]. If I click on [O4] I want to color it together with the reference cell G12. It would be an additional step that I would like to color the cell above [G12] as well.
  I tried to insert this in the formula:</p>
  
  <pre><code>REPLACE(SUBSTITUTE(FORMULATEXT($O$4),"$",""),1,1,"")
  </code></pre>
  
  <p>Which would give back the reference cell [G12] in a normal excel sheet without equal sign but the script doesn't work with it.</p>
  
  <p>Thank you for the support in advance!</p>
  
  <pre><code>Select Case Target.Address
          Case "$O$4" '&lt;~ if cell A1 is clicked, highlight cells C5-C9 yellow
            Cancel = True
            If .Range("O4,G11:G12").Interior.Color = RGB(255, 153, 0) Then
               .Range("O4,G11:G12").Interior.Color = RGB(255, 255, 255)
               .Range("O4,G11:G12").Font.Bold = False
          Else: .Range("O4,G11:G12").Interior.Color = RGB(255, 153, 0)
                .Range("O4,G11:G12").Font.Bold = True
            End If
  End Select
  </code></pre>