Summary based on highlighted cells

[Vic]

I have asked this before, but got no answer - just more questions. I have
modified my request.

I need to create a macro to add the following numbers in cell A based on
highlighted cells in that row:
Add 17 in cell A if cell G is highlighted
Add 10 in cell A for each cell from H thru P that is highlighted
Add 1 in cell A for each cell from Q thru AA that is highlighted
Add 11 in cell A if cell AB is highlighted
Add 1 in cell A for each cell from AC thru AE that is highlighted

I need to do this for every row of my spreadsheet. By highlight I mean any
color except for white (no color). This macro should be executed by me
whenever all updates are in and I need totals.

Thank you.

 

[Luke M]

This macro checks for any type of highlighting (note that formatting a cell
with white background is different than "no fill") and calculated based on
the values you gave. Change the one "For" line values as appropriate for your
spreadsheet.

Sub HighlightCheck()

Dim xValue As Integer
Dim i As Integer


'Change these to your upper and
'lower boundaries for rows
For i = 2 To 10

xValue = 0
If Range("G" & i).Interior.ColorIndex <> xlNone Then
xValue = xValue + 17
End If

For Each cell In Range("H" & i & "" & i)
If cell.Interior.ColorIndex <> xlNone Then
xValue = xValue + 10
End If
Next cell

For Each cell In Range("Q" & i & ":AA" & i)
If cell.Interior.ColorIndex <> xlNone Then
xValue = xValue + 1
End If
Next cell

If Range("AB" & i).Interior.ColorIndex <> xlNone Then
xValue = xValue + 11
End If

For Each cell In Range("AC" & i & ":AE" & i)
If cell.Interior.ColorIndex <> xlNone Then
xValue = xValue + 1
End If
Next cell

Range("A" & i).Value = xValue
Next

End Sub

 

[Luke M]

I should also mention that conditional formats are not detected either. If
you're using conditional formats though, you could build a formula based on
those, so this shouldn't be a problem.

 

[Vic]

Thank you Luke,
How can I change to check for yellow and red highlighted cell only?
Apparently, there was a grey color that should be OK and I should not count
this in. Each cell represents a visit with the visit date or "&" for
scheduled visits with no dates. All other potential visits are blank and
grey. All visits with missing documentation are colored in yellow. All visits
with bad documentation are colored in red. I need to count missing and bad
documentation. Depending on the visit type it could be 1 or 10 or 11 or 17
pages.
Thank you.

 

[Luke M]

We can change the macro to search for color indexex 3 and 6 (red and yellow
respectively).

Sub HighlightCheck()

Dim xValue As Integer
Dim i As Integer


'Change these to your upper and
'lower boundaries for rows
For i = 2 To 10

xValue = 0
'Yellow = 6
'Red = 3
If Range("G" & i).Interior.ColorIndex = 6 Or _
Range("G" & i).Interior.ColorIndex = 3 Then
xValue = xValue + 17
End If

For Each cell In Range("H" & i & "" & i)
If cell.Interior.ColorIndex = 6 Or _
cell.Interior.ColorIndex = 3 Then
xValue = xValue + 10
End If
Next cell

For Each cell In Range("Q" & i & ":AA" & i)
If cell.Interior.ColorIndex = 6 Or _
cell.Interior.ColorIndex = 3 Then
xValue = xValue + 1
End If
Next cell

If Range("AB" & i).Interior.ColorIndex = 6 Or _
Range("AB" & i).Interior.ColorIndex = 3 Then
xValue = xValue + 11
End If

For Each cell In Range("AC" & i & ":AE" & i)
If cell.Interior.ColorIndex = 6 Or _
cell.Interior.ColorIndex = 3 Then
xValue = xValue + 1
End If
Next cell

Range("A" & i).Value = xValue
Next

End Sub