Conditional Formatting Issue

[jegentry]

Good Afternoon!

I am having trouble creating a conditional format that changes the cell colors based on a running total. Such that, when the total reaches 600 hours the format changes to another color. The problem is there will never be a time when the hours can be esxactly 600. How do I create a conditional format either in VBA or thru the "new rule" to format the hours from 1 to 600 with one type of formatting and then the hours between 601 to 1200 with another type of formatting? My cells are non-adjacent and thi is a calendar of sorts. please see pic for example. I want the cell that goes over 600 to have a gradient from the 1st format to move into the new format assocaite with hours between 601 and 1200.

 

[jegentry]

If anyone is interested I found my own answer and put it into VBA Code. If anyone has a more elegant solution I am still interested in another way to do this. Please see below:

Sub colorize()
Dim myrow As Integer, mycol As Integer, counter As Integer
colstart = 7: colend = 37
rowstart = 11: rowend = 12


start:
For mycol = colstart To colend

    For myrow = rowstart To rowend
    
                counter = counter + Cells(myrow, mycol).Value
                
                    If counter <= 600 Then
                            If Cells(myrow, mycol).Value >= 1 Then
                                ' Color Green ------------------------------------------------
                                With Cells(myrow, mycol).Interior
                                    .Pattern = xlSolid
                                    .PatternColorIndex = xlAutomatic
                                    .ThemeColor = xlThemeColorAccent6
                                    .TintAndShade = 0.599993896298105
                                    .PatternTintAndShade = 0
                                End With
                            End If
                    
                    ElseIf counter >= 601 And counter <= 1200 Then
                            If Cells(myrow, mycol).Value >= 1 Then
                                ' Color Blue ------------------------------------------------
                                With Cells(myrow, mycol).Interior
                                    .Pattern = xlSolid
                                    .PatternColorIndex = xlAutomatic
                                    .ThemeColor = xlThemeColorAccent5
                                    .TintAndShade = 0.599993896298105
                                    .PatternTintAndShade = 0
                                End With
                            End If
                    
                    ElseIf counter >= 1201 Then
                            If Cells(myrow, mycol).Value >= 1 Then
                                ' Color Orange ------------------------------------------------
                                With Cells(myrow, mycol).Interior
                                    .Pattern = xlSolid
                                    .PatternColorIndex = xlAutomatic
                                    .ThemeColor = xlThemeColorAccent4
                                    .TintAndShade = 0.599993896298105
                                    .PatternTintAndShade = 0
                                End With
                            End If
                    
                    End If
    Next myrow

Next mycol

colstart = 7
rowstart = rowstart + 5
rowend = rowend + 5

If rowstart > 67 Then Exit Sub Else GoTo start


End Sub