Conditional Formatting not working as expected.

Bvsn. Kiran

Hi,

Excel: 2016 Version.

I am creating conditional formatting dynamically from VBA with the below code every time when I opened my excel. The rules are getting created, but they are not highlighting the max and min value in a row (conditional formatting 3 and 4).

I am so confused, anything I am changing in this code is crashing excel to repair all formatting. Could you please help.

PS: My data is alphanumeric and only contains either "D" or "U" at the end of a number. Also, these are for reference purpose only, wont be considered when calculating max value.

  Set myRng = Direction.Range("A3:U750")

  myRng.FormatConditions.Delete

 myRng.FormatConditions.Add Type:=xlExpression, Formula1:="=VALUE(SUBSTITUTE(SUBSTITUTE(A3,""D"",""""),""U"",""""))>=20"     

  With myRng.FormatConditions(1)

      .Font.Bold = False

      .Font.TintAndShade = 0

      .Interior.Pattern = xlPatternRectangularGradient

      .Interior.Gradient.RectangleLeft = 0.5

      .Interior.Gradient.RectangleRight = 0.5

      .Interior.Gradient.RectangleTop = 0.5

      .Interior.Gradient.RectangleBottom = 0.5

      .Interior.Gradient.ColorStops.Add(0).ThemeColor = xlThemeColorDark1

      .Interior.Gradient.ColorStops.Add(0).TintAndShade = 0

      .Interior.Gradient.ColorStops.Add(1).Color = 13168861

      .Interior.Gradient.ColorStops.Add(1).TintAndShade = 0

      .StopIfTrue = False

      .SetFirstPriority

  End With

  myRng.FormatConditions.Add Type:=xlExpression, Formula1:="=VALUE(SUBSTITUTE(SUBSTITUTE(A3,""D"",""""),""U"",""""))<=-20"           

  With myRng.FormatConditions(2)

      .Font.Bold = False

      .Font.TintAndShade = 0

      .Interior.Pattern = xlPatternRectangularGradient

      .Interior.Gradient.RectangleLeft = 0.5

      .Interior.Gradient.RectangleRight = 0.5

      .Interior.Gradient.RectangleTop = 0.5

      .Interior.Gradient.RectangleBottom = 0.5

      .Interior.Gradient.ColorStops.Add(0).ThemeColor = xlThemeColorDark1

      .Interior.Gradient.ColorStops.Add(0).TintAndShade = 0

      .Interior.Gradient.ColorStops.Add(1).Color = 15525371

      .Interior.Gradient.ColorStops.Add(1).TintAndShade = 0

      .StopIfTrue = False

      .SetFirstPriority

  End With

  myRng.FormatConditions.Add Type:=xlExpression, Formula1:="=AND((VALUE(SUBSTITUTE(SUBSTITUTE(A3,""D"",""""),""U"",""""))>0), VALUE(SUBSTITUTE(SUBSTITUTE(A3,""D"",""""),""U"","""")) = MAX(IFERROR(VALUE(SUBSTITUTE(SUBSTITUTE($A3:$U3,""D"",""""),""U"","""")),"""")))"                 

  With myRng.FormatConditions(3)

      .Font.Bold = True

      .Font.TintAndShade = 0

      .Interior.Pattern = xlPatternRectangularGradient

      .Interior.Gradient.RectangleLeft = 0.5

      .Interior.Gradient.RectangleRight = 0.5

      .Interior.Gradient.RectangleTop = 0.5

      .Interior.Gradient.RectangleBottom = 0.5

      .Interior.Gradient.ColorStops.Add(0).ThemeColor = xlThemeColorDark1

      .Interior.Gradient.ColorStops.Add(0).TintAndShade = 0

      .Interior.Gradient.ColorStops.Add(1).Color = 4123227 '15525371

      .Interior.Gradient.ColorStops.Add(1).TintAndShade = 0

      .StopIfTrue = False

      .SetFirstPriority

  End With

  myRng.FormatConditions.Add Type:=xlExpression, Formula1:="=AND((VALUE(SUBSTITUTE(SUBSTITUTE(A3,""D"",""""),""U"",""""))<0), VALUE(SUBSTITUTE(SUBSTITUTE(A3,""D"",""""),""U"",""""))= MIN(IFERROR(VALUE(SUBSTITUTE(SUBSTITUTE($A3:$U3,""D"",""""),""U"","""")),"""")))"                        

  With myRng.FormatConditions(4)

      .Font.Bold = True

      .Font.TintAndShade = 0

      .Interior.Pattern = xlPatternRectangularGradient

      .Interior.Gradient.RectangleLeft = 0.5

      .Interior.Gradient.RectangleRight = 0.5

      .Interior.Gradient.RectangleTop = 0.5

      .Interior.Gradient.RectangleBottom = 0.5

      .Interior.Gradient.ColorStops.Add(0).ThemeColor = xlThemeColorDark1

      .Interior.Gradient.ColorStops.Add(0).TintAndShade = 0

      .Interior.Gradient.ColorStops.Add(1).Color = 5412584 '15525371

      .Interior.Gradient.ColorStops.Add(1).TintAndShade = 0

      .StopIfTrue = False

      .SetFirstPriority

  End With

  myRng.FormatConditions.Add Type:=xlExpression, Formula1:="=IF((bcolor <> 24),ISBLANK(AD3:AU725)=TRUE)" '1

  With myRng.FormatConditions(5)

    .Interior.Pattern = xlNone

    .Interior.TintAndShade = 0

    .StopIfTrue = False

    .SetFirstPriority

  End With

Bvsn. Kiran

any suggestions is greatly helpful,

the data is alphanumeric that ends with either D or U