Advice for conditional formatting not working

[ipbr21054]

Hi,
The vba below kills the conditional formatting.
With the code removed CD works, but as soon as i put it back CD is killed.

My goal is reference to column L only

If present the word DONE then background colour Red
If present the word TO DO then the backgound colour Blue
If present the character £ then the background colour White

Private Sub Worksheet_SelectionChange(ByVal Target As Range) ' THIS WILL COLOUR ACTIVE CELL & KEEP INTERIOR COLOUR ONCE LEFT HAS BEEN LEFT
    Dim myStartCol As String
    Dim myEndCol As String
    Dim myStartRow As Long
    Dim myLastRow As Long
    Dim myRange As Range

    If Target.Cells.Count > 1 Then Exit Sub
    If Target.Value = "NEVER" Or Target.Value Like "2###" Then Exit Sub


    Application.ScreenUpdating = False
    
'   *** Specify columns to apply this to ***
    myStartCol = "A"
    myEndCol = "K"

'   *** Specify start row ***
    myStartRow = 5
    
'   Use first column to find the last row
    myLastRow = Cells(Rows.Count, myStartCol).End(xlUp).Row
    
'   Build range to apply this to
    Set myRange = Range(Cells(myStartRow, myStartCol), Cells(myLastRow, myEndCol))
    
'   Check to see if cell selected is outside of range
    If Intersect(Target, myRange) Is Nothing Then Exit Sub
    
    With Range("A" & Target.Row & ":K" & Target.Row)
        .Worksheet.Cells.FormatConditions.Delete
        .FormatConditions.Add xlExpression, , True
        .FormatConditions(1).Interior.Color = vbWhite
    End With
    
    With Sheet7.DTPicker1
    .Height = 40
    .Width = 40
    If Not Intersect(Target, Range("F5:G30")) Is Nothing And Len(Target) > 0 Then
      .Visible = True
      .Top = Target.Top
      .Left = Target.Offset(0, 1).Left
      .LinkedCell = Target.ADDRESS
    Else
      .Visible = False
    End If
  End With

  End Sub

 

[rlv01]

With Range("A" & Target.Row & ":K" & Target.Row)
        .Worksheet.Cells.FormatConditions.Delete
        .FormatConditions.Add xlExpression, , True
        .FormatConditions(1).Interior.Color = vbWhite
    End With

Your code above is deleting the format condition, but not replacing it with anything because you do not define a formula for your expression. Normally, the above would reference a formula. For example.

    With Range("A" & Target.Row & ":K" & Target.Row)
        .Worksheet.Cells.FormatConditions.Delete
        .FormatConditions.Add Type:=xlExpression, Formula1:="=$L2=100"
        .FormatConditions(1).Interior.Color = vbRed
    End With

My goal is reference to column L only

If present the word DONE then background colour Red
If present the word TO DO then the backgound colour Blue
If present the character £ then the background colour White

You can use the excel macro recorder to record your self setting up the above conditions to your liking, then edit that code into your macro.