(Solved) – Coloring of a referenced cell (Excel, VBA)

(solved)-–-coloring-of-a-referenced-cell-(excel,-vba)

If I have a column of referenced numbers and I would like to color the referenced cell automatically after right clicking, how I can 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 refenerce 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:

REPLACE(SUBSTITUTE(FORMULATEXT($O$4),”$”,””),1,1,””)

Which would give back the reference cell [G12] in a normal excel sheet without equal sign but the script doesn’t work with it.

Thank you for the support in advance!

Select Case Target.Address
        Case "$O$4" '<~ 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

Leave a Reply

Your email address will not be published. Required fields are marked *