Thread: Application.volatile not working

Hi all,

I have a piece of code in a workbook (module 2) to change the colour of certain text when selected from a drop down list. Sometimes the colour changes, sometimes it doesn't it seems pretty random. I want the colour to change as soon as it's selected, I don't want to have to run the macro manually Here is the code:

Sub OTcolour()
Application.Volatile True

ActiveSheet.Unprotect Password:="fire"
    Dim Values As Range
    Dim cell As Variant
    Set Values = Range("A3:F28")
    For Each cell In Values
        If cell.Value = "OT" Or cell.Value = "A/P/C" Or cell.Value = "A/C" Or cell.Value = "DE-IN" _
        Or cell.Value = "DE-IN AC" Or cell.Value = "DE-IN APC" Or cell.Value = "OT-AC" Or cell.Value = "OT-APC" Then
            cell.Font.ColorIndex = 10
                End If
                Next cell
ActiveSheet.Protect Password:="fire", userinterfaceonly:=True
End Sub

So, if I select "OT" in cell B6, the colour changes - great...but if I select "OT" in cell B10 - nothing. Not sure where I've gone wrong but any help would be appreciated. Workbook attached
TIA