Conditional formatting on sub-groups and whole data at the same time

I'm trying to create a custom formatting rule for a table where I have multiple columns that are grouped into sub-groups, like in below. It would be great if I can see at the same time from the table that a) which is the lowest value in a sub-group (setting) and b) which is the sub-group with lowest average (or lowest absolute) value.

Is it possible to create overlapping conditional rules in Excel, so that I can visualize these two things at the same time?

To summarise, the setting with the lowest average should be filled in with different shades of green, the one with the highest average should be filled in with different shades of red, and the remaining one should be filled in with different shades of yellow/orange/amber. Within each setting, the lowest value should be filled in with a different shade to the other values.

You can do it semi-manually by working out what the lower and upper limits for the three settings would have to be to move the formatting into the amber area, green area and red area respectively.

It can be shown with some algebra if

l is the minimum data item

h is the max data item

a is the lower required fraction

b is the upper required fraction

that the lower limit should be

(a*h-b*l)/(a-b)

and the upper limit

(l-h+a*h-b*l)/(a-b)

You can create a table like this

and enter the lower and upper limits into a three-colour format for each of the three settings.

This could be automated using VBA.

---

Have started so will finish even though question closed while I wasn't looking.

Here is some sample VBA code.

Option Base 1

Option Explicit

Sub Test()

   Dim r(3) As range
   Dim av(3) As Double
   Dim a As Double, b As Double, l As Double, h As Double
   Dim lower As Double, upper As Double, middle As Double
   Dim i As Integer
   
   Set r(1) = range("B2:B4")
   Set r(2) = range("B6:B8")
   Set r(3) = range("B10:B12")
   
   For i = 1 To 3
   av(i) = Application.WorksheetFunction.Average(r(i))
   Next i
   
   ' Calculate limits for three settings
 
   For i = 1 To 3
       
       If av(i) = Application.WorksheetFunction.Max(av) Then
           a = 0.7
           b = 1
       ElseIf av(i) = Application.WorksheetFunction.Min(av) Then
               a = 0
               b = 0.3
       Else
                   a = 0.35
                   b = 0.65
       End If
   
       l = Application.WorksheetFunction.Min(r(i))
       h = Application.WorksheetFunction.Max(r(i))
   
       lower = (a * h - b * l) / (a - b)
       upper = (l - h - b * l + a * h) / (a - b)
       middle = (upper + lower) / 2
   
       Call Fmt1(lower, middle, upper, r(i))
   
   Next i
           
           
End Sub

Sub Fmt1(lower As Double, middle As Double, upper As Double, fmtRange As range)

    ' Format green - red according to given limits

   fmtRange.FormatConditions.Delete

   fmtRange.FormatConditions.AddColorScale ColorScaleType:=3
   fmtRange.FormatConditions(fmtRange.FormatConditions.Count).SetFirstPriority
   fmtRange.FormatConditions(1).ColorScaleCriteria(1).Type = _
       xlConditionValueNumber
   fmtRange.FormatConditions(1).ColorScaleCriteria(1).Value = lower
   With fmtRange.FormatConditions(1).ColorScaleCriteria(1).FormatColor
       .Color = 8109667
       .TintAndShade = 0
   End With
   fmtRange.FormatConditions(1).ColorScaleCriteria(2).Type = _
       xlConditionValueNumber
   fmtRange.FormatConditions(1).ColorScaleCriteria(2).Value = middle
   With fmtRange.FormatConditions(1).ColorScaleCriteria(2).FormatColor
       .Color = 8711167
       .TintAndShade = 0
   End With
   fmtRange.FormatConditions(1).ColorScaleCriteria(3).Type = _
       xlConditionValueNumber
   fmtRange.FormatConditions(1).ColorScaleCriteria(3).Value = upper
   With fmtRange.FormatConditions(1).ColorScaleCriteria(3).FormatColor
       .Color = 7039480
       .TintAndShade = 0
   End With
End Sub