Automatic color change of a rectangle when cell changes.

[pauldobie1966]

Hi all,
This is my first time posting and I am a very amateur excel operator.
I have a project which I am making where I need a rectangle to change automatically when the text inside a cell changes from C to P.
I am assigning the below macro to the rectangle.
It does not change automatically when I change the text in cell R22 you then have to click the rectangle for it to change color.

Anyones help would be greatly appreciated.

Sub Rectangle33_Change()
Dim ColorNum As Integer

If ActiveSheet.Range("$r$22") = "C" Then
ColorNum = 3 'green
ElseIf ActiveSheet.Range("$r$22") = "P" Then
ColorNum = 5 'yellow
End If

ActiveSheet.Shapes("Rectangle 33").Fill.ForeColor.SchemeColor = ColorNum

[NeedForExcel]

Are you not considering using Conditional Formatting, by changing the Cell Border color instead of the Rectangle?

[pauldobie1966]

Hi
Thank you for your quick response.
I cant use CF because the rectangle I am using is layered on top of a picture image in my document.
I have used CF in many cases in the document and so that is why I have used the macro.