VBA Code to highlight one cell if greater or less than another

[Tryingtoresolve]

I have been trying to change VBA code on an existing spreadsheet with macros I have managed to get the red and green VBA code to work but I can't get the Ambre code to work

What I'm looking for is when U2 is updated with a qty greater than 1 but less than the Qty shown in cell R2 the cell should turn Amber

This is the code I'm using

Range("U2:U" & LR).Select

Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=U2>=1 <=R2"

Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority

With Selection.FormatConditions(1).Interior

.PatternColorIndex = xlAutomatic

.Color = RGB(252, 213, 180) 'Amber

.TintAndShade = 0

End With

Range("U2:U" & LR).Select

Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=U2>R2"

Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority

With Selection.FormatConditions(1).Interior

.PatternColorIndex = xlAutomatic

.Color = 255 'RED

.TintAndShade = 0

End With

Range("U2:U" & LR).Select

Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=U2=R2"

Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority

With Selection.FormatConditions(1).Interior

.PatternColorIndex = xlAutomatic

.Color = RGB(216, 228, 188) 'Green

.TintAndShade = 0

End With

 

[Fluff]

Try

"=and(U2>=1,U2<=R2)"

 

[Tryingtoresolve]

Try

"=and(U2>=1,U2<=R2)"

Thats brilliant and works great thank you

 

[Fluff]

You're welcome & thanks for the feedback.

 

[Tryingtoresolve]

You're welcome & thanks for the feedback.

Fluff I have just posted a new query again

How to stop VBA changing a cell colour until 0 is entered​