Update automatically when using VBA

[Even]

Hi,
this Excel model I am using is working pretty nicely, however, it is not updating like I want. Whenever I select a predefined text in a cell in column C, the cell is suppose to change color depending on what I choose. For example when the cell says "Not started" it should turn red. People have advised me to do conditional formatting, however, I find that it doesn't work after I update the model. So, I have to use VBA.
The code I use is:

For i = 1 To lr
If Cells(i, "C") = "Not started" Then
 Cells(i, "C").Font.Color = vbBlack
 Cells(i, "C").Interior.Color = 10329075
 End If

It works, but just not automatically.

Does anybody know what I am talking about and could help me out?

Thanks

Even

 

[rollis13]

You can use event Worksheet_Change to automatically trigger your macro whenever you change the cell's contents to "Not started". Use this to be pasted in the sheet's module:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("C:C")) Is Nothing Then Exit Sub
    If Target.CountLarge > 1 Then Exit Sub
    If Target = "Not started" Then
        Target.Font.Color = vbBlack
        Target.Interior.Color = 10329075
    End If
End Sub