CODEWITHSUNDEEP
vbaexcel
dashton
dashton

Reputation: 43

Print Quantity of Cells of a Certain Color

Sub PercentCompletePipes()
Dim k As Range
Dim Counter As Integer
Dim Green As Integer
Dim Red As Integer
Red = 0
Green = 0
xTitleId = "Percentage Completed Inverts"
MsgBox "This macro defines the percentage of pipes with completed inverts.  It ignores all PRIVATE pipes."
MsgBox "WARNING: This macro only works with COMPLETED invert excel sheets."
For Each k In ActiveSheet.UsedRange.Rows
    Counter = 0
    If Counter >= 4 Then
        If ActiveSheet.Cells(k.Row, 1).Interior.ColorIndex = 4 Then
            Green = Green + 1
        ElseIf ActiveSheet.Cells(k.Row, 1).Interior.ColorIndex = 3 Then
            Red = Red + 1
        ElseIf ActiveSheet.Cells(k.Row, 1).Interior.ColorIndex = 6 Then
            ActiveSheet.Cells(k.Row, 1).Value = "COMPLETED PIPES:"
            ActiveSheet.Cells(k.Row, 2).Value = Green
            ActiveSheet.Cells(k.Row + 1, 1).Value = "INCOMPLETE PIPES:"
            ActiveSheet.Cells(k.Row + 1, 2).Value = Red
            ActiveSheet.Cells(k.Row + 2, 1).Value = "PERCENTAGE COMPLETE:"
            ActiveSheet.Cells(k.Row + 2, 2).Value = Green / (Red + Green)
        End If
    End If
    Counter = Counter + 1
Next k




End Sub

Sorry for the entry level question, but I am trying to print out the completion of rows in my excel sheet based on whether or not the 1st cell in each row is green or red (green being complete, red being incomplete). It is supposed to ignore all other colors (grey, white etc.). When I use the code snippet posted above, no output is generated. It is supposed to generate an output once I highlight the first cell in a row yellow (color index = 6). Image of excel file I'm trying to analyze.

Upvotes: 1

Views: 23

Answers (1)

dashton
dashton

Reputation: 43

Sub PercentCompletePipes()
Dim k As Range
Dim Counter As Integer
Dim Green As Integer
Dim Red As Integer
Red = 0
Green = 0
Counter = 0
xTitleId = "Percentage Completed Inverts"
MsgBox "This macro defines the percentage of pipes with completed inverts.  It ignores all PRIVATE pipes."
MsgBox "WARNING: This macro only works with COMPLETED invert excel sheets."
For Each k In ActiveSheet.UsedRange.Rows
    If Counter >= 4 Then
        If ActiveSheet.Cells(k.Row, 1).Interior.ColorIndex = 4 Then
            Green = Green + 1
        ElseIf ActiveSheet.Cells(k.Row, 1).Interior.ColorIndex = 3 Then
            Red = Red + 1
        ElseIf ActiveSheet.Cells(k.Row, 1).Interior.ColorIndex = 6 Then
            ActiveSheet.Cells(k.Row, 1).Value = "COMPLETED PIPES:"
            ActiveSheet.Cells(k.Row, 2).Value = Green
            ActiveSheet.Cells(k.Row + 1, 1).Value = "INCOMPLETE PIPES:"
            ActiveSheet.Cells(k.Row + 1, 2).Value = Red
            ActiveSheet.Cells(k.Row + 2, 1).Value = "PERCENTAGE COMPLETE:"
            ActiveSheet.Cells(k.Row + 2, 2).Value = Green / (Red + Green)
        End If
    End If
    Counter = Counter + 1
Next k




End Sub

This fixed it. I had put the counter variable in the for loop mistakenly.

Upvotes: 0

Related Questions