[SOLVED] How do I change my VBA formatting code so that the rule would read the cell value constantly and not only copy the values to the rule?

How do I change my VBA formatting code so that the rule would read the cell value constantly and not only copy the values to the rule?

I'm writing a code which paints the number in the cell red if the value is not between the values in two seperate cells. The problem is that my code writes the values from the cells to the conditional formatting rule and not a formula which would detect changes in those two cells. So if I change any of the values in the two seperate cells, the rule remains the same, because the values don't get changed. I know how to manually change the formula in the rule but don't know how to change it in the VBA code. If my blabbering is not understandable the 2 pictures explain the situation better. The rule my macro creates The rule I want my macro to create My current code:

Sub formatting()

  Dim rng As Range
  Dim condition1 As FormatCondition
  
  Set rng = Range("E3")


   Set condition1 = rng.FormatConditions.Add(xlCellValue, Operator:=xlNotBetween, _
   Formula1:=Range("=$C$3"), Formula2:=Range("=$D$3"))
   
  
   With condition1
   .Font.Color = vbRed
   End With
   
End Sub

I don't know how to change the ranges in Formula1 and Formula2 so that they wouldn't just copy the values to the ruels. Any help is greatly appreciated!

I've already tried changing the Formulas as shown: Formula1:=Range("C3") Formula2:=Range("D3") but that didn't work.

Solution

You can do this:

Set condition1 = rng.FormatConditions.Add(xlCellValue, Operator:=xlNotBetween, _
    Formula1:="=$C$3", Formula2:="=$D$3")