» Set up conditional formatting for an area with VBA in Microsoft Excel

Question:
In the selected area, the maximum value is to be marked violet with a conditional formatting


Answer:
Enter following code in a standard module.

 Sub ConditionalFormat()

   With Selection

      .FormatConditions.Delete

      .FormatConditions.Add Type:=xlExpression, Formula1:= _

        "=" & Range("Field").Cells(1).Address(False, False) & _

         "=MAX(" & Selection.Address & ")"

      .FormatConditions(1).Interior.ColorIndex = 39

   End With

End Sub