Sub Macro1()
'
' Macro1 Macro
'
'
Dim runningTotal As Range
Dim runTotCounter As Integer
Dim subRangeCounter As Integer
Dim subRange0 As Range
Dim subRange1 As Range
Dim subRange2 As Range
Dim subRange3 As Range
Dim subRange4 As Range
Dim subRange5 As Range
Dim subRange6 As Range
Dim subRange7 As Range


subRangeCounter = 0
reference = Range("A4").Value

ThisWorkbook.Sheets(7).Range("A:JL").FormatConditions.Delete ' Delete current scales

For Each c In Worksheets("PVP Calculator").Range("A7:JL1506").Cells
 '   If c.Interior.colorIndex <> 0 Then
  '      c.Interior.colorIndex = 0
   ' End If
    If c.Value >= reference - 5 And c.Value <= reference Then
 '       c.Interior.colorIndex = 6
        If runningTotal Is Nothing Then
            Set runningTotal = c
        Else
            Set runningTotal = Union(runningTotal, c)
        End If
        runTotCounter = runTotCounter + 1
        If runTotCounter > 200 Then
            runTotCounter = 0
            If subRangeCounter = 0 Then
                Set subRange0 = runningTotal
            End If
            If subRangeCounter = 1 Then
                Set subRange1 = runningTotal
            End If
            If subRangeCounter = 2 Then
                Set subRange2 = runningTotal
            End If
            If subRangeCounter = 3 Then
                Set subRange3 = runningTotal
            End If
            If subRangeCounter = 4 Then
                Set subRange4 = runningTotal
            End If
            If subRangeCounter = 5 Then
                Set subRange5 = runningTotal
            End If
            If subRangeCounter = 6 Then
                Set subRange6 = runningTotal
            End If
            If subRangeCounter = 7 Then
                Set subRange7 = runningTotal
            End If
            Set runningTotal = ""
            MsgBox ("made it")
            subRangeCounter = subRangeCounter + 1
        End If
    End If
Next
MsgBox (runTotCounter)
If subRangeCounter = 0 And runningTotal Is Nothing Then
    Set runningTotal = Range("JL1506") ' We're gonna do a little something something where the max possible stat is used if we can't get to 1500
End If

' We delete the existing names, in a troubleshooting attempt to figure out why a second run through resulted in an error for the line below

runningTotal.Name = "runTot"
If Not subRange0 Is Nothing Then
    subRange0.Name = "sub0"
End If
If Not subRange1 Is Nothing Then
    subRange1.Name = "sub1"
End If
If Not subRange2 Is Nothing Then
    subRange2.Name = "sub2"
End If
If Not subRange3 Is Nothing Then
    subRange3.Name = "sub3"
End If
If Not subRange4 Is Nothing Then
    subRange4.Name = "sub4"
End If
If Not subRange5 Is Nothing Then
    subRange5.Name = "sub5"
End If
If Not subRange6 Is Nothing Then
    subRange6.Name = "sub6"
End If
If Not subRange7 Is Nothing Then
    subRange7.Name = "sub7"
End If

If subRangeCounter = 0 Then
    Set master = runningTotal
End If
If subRangeCounter = 1 Then ' remember the counter has gone up by 1 more than a "filled" subRange
    Set master = Union(runningTotal, subRange0)
End If
If subRangeCounter = 2 Then
    Set master = Union(runningTotal, subRange0, subRange1)
End If
If subRangeCounter = 3 Then
    Set master = Union(runningTotal, subRange0, subRange1, subRange2)
End If
If subRangeCounter = 4 Then
    Set master = Union(runningTotal, subRange0, subRange1, subRange2, subRange3)
End If
If subRangeCounter = 5 Then
    Set master = Union(runningTotal, subRange0, subRange1, subRange2, subRange3, subRange4)
End If
If subRangeCounter = 6 Then
    Set master = Union(runningTotal, subRange0, subRange1, subRange2, subRange3, subRange4, subRange5)
End If
If subRangeCounter = 7 Then
    Set master = Union(runningTotal, subRange0, subRange1, subRange2, subRange3, subRange4, subRange5, subRange6)
End If
If subRangeCounter = 8 Then
    Set master = Union(runningTotal, subRange0, subRange1, subRange2, subRange3, subRange4, subRange5, subRange6, subRange7)
End If

master.Name = "master"

Range("master").Select

' I took out the gradient because it was broken when I added in a tolerance to the target ranges.

' This highlights every other applicable cell on a gradient
'    Selection.FormatConditions.AddColorScale ColorScaleType:=2
'    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
'    Selection.FormatConditions(1).ColorScaleCriteria(1).Type = _
'        xlConditionValueLowestValue
'    With Selection.FormatConditions(1).ColorScaleCriteria(1).FormatColor
'        .Color = 16776444
'        .TintAndShade = 0
'    End With
'    Selection.FormatConditions(1).ColorScaleCriteria(2).Type = _
'        xlConditionValueHighestValue
'    With Selection.FormatConditions(1).ColorScaleCriteria(2).FormatColor
'        .Color = 8109667
'        .TintAndShade = 0
'    End With

 ' Then add the #1 formatting, which is Bold and Gold; apparently what is added last is the highest priority
    Selection.FormatConditions.AddTop10
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1)
        .TopBottom = xlTop10Top
        .Rank = 1
        .Percent = False
    End With
    With Selection.FormatConditions(1).Font
        .Bold = True
        .Italic = False
 '       .TintAndShade = 0 ' Damn thing keeps throwing errors despite being sourced from macro recorder
    End With
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 49407
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False

'Range("L3").formula = "=Max(" & runningTotal.Address & ")"
' But using a name range, I get this perk
Range("L3").formula = "=MAX(master)"
End Sub