Reports

First off thank you Tim Williams.

Final Code:
Numbers are different because I have two different data sets I'm going to be working with.
`

Sub HourColorsLong()
    Dim rg As Range
    Dim cs As ColorScale
 
    For Each rg In Selection.Cells
        If rg.Value < 4.75 Then
            Set cs = rg.FormatConditions.AddColorScale(ColorScaleType:=2)
                cs.ColorScaleCriteria(1).Type = xlConditionValueFormula
                cs.ColorScaleCriteria(1).Value = "0"
                cs.ColorScaleCriteria(1).FormatColor.Color = RGB(255, 0, 0)
                cs.ColorScaleCriteria(1).FormatColor.TintAndShade = 0
                cs.ColorScaleCriteria(2).Type = xlConditionValueFormula
                cs.ColorScaleCriteria(2).Value = "4.75"
                cs.ColorScaleCriteria(2).FormatColor.Color = RGB(255, 255, 0)
                cs.ColorScaleCriteria(2).FormatColor.TintAndShade = 0
                
        ElseIf rg.Value <= 14.25 Then
            Set cs = rg.FormatConditions.AddColorScale(ColorScaleType:=3)
                cs.ColorScaleCriteria(1).Type = xlConditionValueFormula
                cs.ColorScaleCriteria(1).Value = "4.75"
                cs.ColorScaleCriteria(1).FormatColor.Color = RGB(255, 255, 0)
                cs.ColorScaleCriteria(1).FormatColor.TintAndShade = 0
                cs.ColorScaleCriteria(2).Type = xlConditionValueFormula
                cs.ColorScaleCriteria(2).Value = "9.5"
                cs.ColorScaleCriteria(2).FormatColor.Color = RGB(0, 255, 0)
                cs.ColorScaleCriteria(2).FormatColor.TintAndShade = 0
                cs.ColorScaleCriteria(3).Type = xlConditionValueFormula
                cs.ColorScaleCriteria(3).Value = "14.25"
                cs.ColorScaleCriteria(3).FormatColor.Color = RGB(255, 255, 0)
                cs.ColorScaleCriteria(3).FormatColor.TintAndShade = 0
                
        ElseIf rg.Value > 14.25 Then
            Set cs = rg.FormatConditions.AddColorScale(ColorScaleType:=2)
                cs.ColorScaleCriteria(1).Type = xlConditionValueFormula
                cs.ColorScaleCriteria(1).Value = "14.25"
                cs.ColorScaleCriteria(1).FormatColor.Color = RGB(255, 255, 0)
                cs.ColorScaleCriteria(1).FormatColor.TintAndShade = 0
                cs.ColorScaleCriteria(2).Type = xlConditionValueFormula
                cs.ColorScaleCriteria(2).Value = "19"
                cs.ColorScaleCriteria(2).FormatColor.Color = RGB(255, 0, 0)
                cs.ColorScaleCriteria(2).FormatColor.TintAndShade = 0
        End If
    Next
End Sub

So things to note:
1 ColorScaleType:=3, scaling will not work if you only give it two values. Had to fix this to ColorScaleType:=2.
2 I had 14.25 set to less than rather than greater than.
3 Setting a range to selection then checking a range withing that range will not work and that's why I believe I was getting type mismatch.

Once again thank you for the help. Hopefully this helps other people too.

79526357