I’m struggling with writing the code which would create a conditional formatting rule in the E3 cell. I want the rule to paint the numberin the E3 cell if the value is not between the value of cells C5 and D5 and to leave it in black color if the value is in between the values of cells. Regular conditional formatting can not be used because the function of this macro includes copying and pasting the content from 1 sheet to another which unfortunatelly deletes the conditional formatting rules. The value in cells C5 and D5 often changes so I can’t just put specific values in the code.

Any help would be greatly appreciated!

Below I have pasted my attempt which does not work. It does create a rule but it is not complete.

  Dim rng As Range
  Dim condition1 As FormatCondition, condition2 As FormatCondition
  


  Set rng = Range("E3")

   rng.FormatConditions.Delete


   Set condition1 = rng.FormatConditions.Add(xlCellValue, Operator:=xlBetween, _
   Formula1:=Range("C5"), Formula2:=Range("D5"))
   
  
   With condition1
    .Font.Color = vbRed
   End With