Conditionally Format cells in a row based on the sum of the previous nth cells.

[elcid7236]

SamT,

Again, Thank you for the code...you have saved me alot of frustrating hours...is it possible to shorten this code even more? here is a sample of what I am doing.

I am looking to do a For...Next loop with the columns. Otherwise, I am writing 362 more iterations of this code. I have 4 right now. As you can see, in each iteration I change the column by one letter.

Any help is always appreciated.

Elcid

Dim Rw As Long
Dim strSteel As String
Dim strRed As String
Dim strYellow As String
Dim strPink As String
Dim strOrange As String
Dim strMajenta As String
Dim strGreen As String

strSteel = RGB(184, 204, 228)
strRed = RGB(255, 0, 0)
strYellow = RGB(255, 255, 0)
strPink = RGB(255, 192, 203)
strOrange = RGB(228, 108, 10)
strMajenta = RGB(204, 0, 255)
strGreen = RGB(0, 204, 0)

' ********************* Column "G" to "NG" *************************
For Rw = 6 To 171 Step 5
'first group of conditional formatting
If Application.WorksheetFunction.Sum(Range("G" & Rw & ":I" & Rw)) >= 30 Then
Range("I" & Rw + 2).Interior.Color = strGreen
End If
If Application.WorksheetFunction.Sum(Range("G" & Rw & ":M" & Rw)) >= 56 Then
Range("M" & Rw + 2).Interior.Color = strMajenta
End If
If Application.WorksheetFunction.Sum(Range("G" & Rw & ":T" & Rw)) >= 70 Then
Range("T" & Rw + 2).Interior.Color = strOrange
End If
If Application.WorksheetFunction.Sum(Range("G" & Rw & ":T" & Rw)) >= 80 Then
Range("T" & Rw + 2).Interior.Color = strPink
End If
If Application.WorksheetFunction.Sum(Range("G" & Rw & ":AJ" & Rw)) >= 110 Then
Range("AJ" & Rw + 2).Interior.Color = strYellow
End If
If Application.WorksheetFunction.Sum(Range("G" & Rw & ":AJ" & Rw)) >= 120 Then
Range("AJ" & Rw + 2).Interior.Color = strRed
End If

'second group of conditional formatting

If Application.WorksheetFunction.Sum(Range("H" & Rw & ":J" & Rw)) >= 30 Then
Range("J" & Rw + 2).Interior.Color = strGreen
End If
If Application.WorksheetFunction.Sum(Range("H" & Rw & ":N" & Rw)) >= 56 Then
Range("N" & Rw + 2).Interior.Color = strMajenta
End If
If Application.WorksheetFunction.Sum(Range("H" & Rw & ":U" & Rw)) >= 70 Then
Range("U" & Rw + 2).Interior.Color = strOrange
End If
If Application.WorksheetFunction.Sum(Range("H" & Rw & ":U" & Rw)) >= 80 Then
Range("U" & Rw + 2).Interior.Color = strPink
End If
If Application.WorksheetFunction.Sum(Range("H" & Rw & ":AK" & Rw)) >= 110 Then
Range("AK" & Rw + 2).Interior.Color = strYellow
End If
If Application.WorksheetFunction.Sum(Range("H" & Rw & ":AK" & Rw)) >= 120 Then
Range("AK" & Rw + 2).Interior.Color = strRed
End If

'third group of conditional formatting

If Application.WorksheetFunction.Sum(Range("I" & Rw & ":K" & Rw)) >= 30 Then
Range("K" & Rw + 2).Interior.Color = strGreen
End If
If Application.WorksheetFunction.Sum(Range("I" & Rw & ":O" & Rw)) >= 56 Then
Range("O" & Rw + 2).Interior.Color = strMajenta
End If
If Application.WorksheetFunction.Sum(Range("I" & Rw & ":V" & Rw)) >= 70 Then
Range("V" & Rw + 2).Interior.Color = strOrange
End If
If Application.WorksheetFunction.Sum(Range("I" & Rw & ":V" & Rw)) >= 80 Then
Range("V" & Rw + 2).Interior.Color = strPink
End If
If Application.WorksheetFunction.Sum(Range("I" & Rw & ":AL" & Rw)) >= 110 Then
Range("AL" & Rw + 2).Interior.Color = strYellow
End If
If Application.WorksheetFunction.Sum(Range("I" & Rw & ":AL" & Rw)) >= 120 Then
Range("AL" & Rw + 2).Interior.Color = strRed
End If

'fourth group of conditional formatting

If Application.WorksheetFunction.Sum(Range("I" & Rw & ":K" & Rw)) >= 30 Then
Range("K" & Rw + 2).Interior.Color = strGreen
End If
If Application.WorksheetFunction.Sum(Range("I" & Rw & ":O" & Rw)) >= 56 Then
Range("O" & Rw + 2).Interior.Color = strMajenta
End If
If Application.WorksheetFunction.Sum(Range("I" & Rw & ":V" & Rw)) >= 70 Then
Range("V" & Rw + 2).Interior.Color = strOrange
End If
If Application.WorksheetFunction.Sum(Range("I" & Rw & ":V" & Rw)) >= 80 Then
Range("V" & Rw + 2).Interior.Color = strPink
End If
If Application.WorksheetFunction.Sum(Range("I" & Rw & ":AL" & Rw)) >= 110 Then
Range("AL" & Rw + 2).Interior.Color = strYellow
End If
If Application.WorksheetFunction.Sum(Range("I" & Rw & ":AL" & Rw)) >= 120 Then
Range("AL" & Rw + 2).Interior.Color = strRed
End If

Next Rw