If we have several intervals where we want to highlight, through conditional formatting, the lowest and the highest value, we can successively use the two options by accessing the menu Home – Conditional Formatting – Top/Bottom Rules.
If we have to use these options several times, a macro applicable to each such selection can be recommended.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 |
Sub TopBottom() Dim switch As Integer, culSus As Variant, culJos As Variant switch = InputBox("Color for highest value: " & _ Chr(10) & "• 1: GREEN;" & _ Chr(10) & "• 0: REED.", "Choose the option", 1) If switch = 1 Then culSus = 13561798 ' The greatest value - green culJos = 13551615 ' The smallest value - red Else culSus = 13551615 ' The greatest value - red culJos = 13561798 ' The smallest value - green End If ' Top10Top Selection.FormatConditions.AddTop10 Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority With Selection.FormatConditions(1) .TopBottom = xlTop10Top .Rank = 1 End With Selection.FormatConditions(1).Interior.Color = culSus Selection.FormatConditions(1).StopIfTrue = False ' Top10Bottom Selection.FormatConditions.AddTop10 Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority With Selection.FormatConditions(1) .TopBottom = xlTop10Bottom .Rank = 1 End With Selection.FormatConditions(1).Interior.Color = culJos Selection.FormatConditions(1).StopIfTrue = False End Sub |
