Cell color

[Closed]
Report
-
Posts
2818
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 18, 2021
-
Hello,

I have a worksheet with various dollar values in it. I want to write a formula to add all the cells that are a certain color (green or yellow). If I change the color to green I want that value added to a summation box. Any ideas?

1 reply

Posts
2818
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 18, 2021
486
Hi Doc,

The following code will look in column A for green and yellow filled cells.
The values of the green filled cells will be added and the result will be placed in B1. The result for the yellow filled cells will be placed in B2.

Here is the code:
Sub RunMe()
Dim lRow As Integer
Dim GreenCount, YellowCount As Long

lRow = Range("A" & Rows.Count).End(xlUp).Row

For Each cell In Range("A1:A" & lRow)
    If cell.Interior.Color = 5287936 Then GreenCount = GreenCount + cell.Value
    If cell.Interior.Color = 65535 Then YellowCount = YellowCount + cell.Value
Next cell

Range("B1").Value = GreenCount
Range("B2").Value = YellowCount
End Sub


Best regards,
Trowa