VBA Font Conditional Formatting based on cell value change - but it doesn't trigger

[hcgood]

Below is my poor attempt at VBA code based on changing cell value in P14.
When P14 changes to 1 it should trigger, but it doesn't trigger.
If I step through it, it correctly changes the font size and color in cell P15.

How do I need to change this code so it will trigger when P14 changes to 1?
Many thanks in advance!

Sub Formatting()

ThisWorkbook.Worksheets("Progress").Range("$P$14").Select
With ThisWorkbook.Worksheets("Progress").Range("$P$15")
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:="=$P$14=1"
.FormatConditions(1).Interior.ColorIndex = 3 'red
With .Font
.ColorIndex = 27 'color Yellow
.Size = 12
.Bold = True
End With
End With
End Sub

 

[sxhall]

Hi hcgood,

You can add the below slightly amended from your original code in to the worksheet module. It will in effect watch cell P14 for a change and when it is 1 it will trigger the code...

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("P14")) Is Nothing Then

    With ThisWorkbook.Worksheets("Progress").Range("$P$15")
    .FormatConditions.Delete
    .FormatConditions.Add Type:=xlExpression, Formula1:="=$P$14=1"
    .FormatConditions(1).Interior.ColorIndex = 3 'red
        With .Font
        .ColorIndex = 27 'color Yellow
        .Size = 12
        .Bold = True
        End With
    End With

End If

End Sub

Where to add the code, double click your sheet 'Progress'...

 

[RoryA]

If I step through it, it correctly changes the font size and color in cell P15

Those aspects are not part of the conditional format, so it would imply that P14 does not actually equal 1 if the background isn't going red.

 

[hcgood]

Hi hcgood,

You can add the below slightly amended from your original code in to the worksheet module. It will in effect watch cell P14 for a change and when it is 1 it will trigger the code...

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("P14")) Is Nothing Then

    With ThisWorkbook.Worksheets("Progress").Range("$P$15")
    .FormatConditions.Delete
    .FormatConditions.Add Type:=xlExpression, Formula1:="=$P$14=1"
    .FormatConditions(1).Interior.ColorIndex = 3 'red
        With .Font
        .ColorIndex = 27 'color Yellow
        .Size = 12
        .Bold = True
        End With
    End With

End If

End Sub

Where to add the code, double click your sheet 'Progress'...
View attachment 119774

Thanks sxhall. That solved that part.
But I'd assumed that if P14 no longer equals 1 that it would go back to it's original format, but it doesn't.
Seems I need a second step so that if P14 not equal to 1 that it goes back to Color = Black, Size = 16.
I don't know if an Else statement is needed or how to make it change back to original font.

Many thanks