Counting Cells VBA

[bagos]

I've been searching all forums related to my question for quite some time and I cant seent to find the answer. I have an undetermined amount of worksheets. All worksheets are formated exactly the same. Each worksheet has a list of questions (33 of them) with Yes, No and N/A answers as a drop down option. Each sheet also has one drop down option with the colors Red, White, Black, Green and Purple. I need a macro that will count and add the "Yes" from Cell A3 from all sheets that have the criteria of the spefic color and then place that number in another sheet titled X. With this final number I'm planing on doing a percentage on all sheets. So if I have 2 worksheets that have the value "Yes" in A3 and are "Red" and two others that have the value "No" in A3 and are "Red" as well I want 50% on the last X sheet, cell "whatever". And if I have two worksheets with "Yes" on A16 and are "Green" and 8 worksheets with "No" on A16 and are "Green" then I will get a value of 20%.
I have very limited knowledge on VBA and the following is a humble try at this massive issue. For now I'm just trying to the count on a msgbox but it keeps poping a msgbox for each sheet with the number one. Any help would be great.


Sub Percentage()

Dim truevalue, colorvalue As String
Dim ws As Worksheet
Dim ct As Double

For Each ws In worksheets

ws.Select
colorvalue = Range("A3").Value
truevalue = Range("D6").Value

If colorvalue = "Red" And truevalue = "Yes" Then
ct = ws.Range("D6").Count
MsgBox ct

End If

If colorvalue = "Green" Then

MsgBox "Green is Good" 'Here goes what needs to be done when is green

End If

Next

End Sub

 

[XLfriend777]

Hi bagos - Welcome to the forum! I think you are very close with your code. You might consider the code below. It will take the name of each worksheet and place it in column A of worksheet "x" starting at A1. You could then do the same for the values you want and place them in column B and the colors in column C. It would then be easy to go into worksheet "x" and calculate your data. Hope this helps get you started.

Sub bagos_List_Sheets()
'List names of all worksheets in workbook and place them in column A starting at A1
Sheets("x").Select
    For i = 1 To Sheets.count
        Cells(i, 1).Select
        Cells(i, 1) = Sheets(i).Name
    Next i
End Sub