How to count cells with blue colour into cell on another sheet?

Question

I want to count cells with blue colour into cell on another sheet.
Sheet with coloured cells is "MAPA" and sheet where is the result is "Skupinka 1"

Private Sub Skupinka1_obnov1_Click()
Dim counter As Integer
For counter = 4 To 26

    If Worksheets("MAPA").Cells(counter, 1).Interior.ColorIndex = 5 Then
        Worksheets("Skupinka 1").Cells(32, 20) = Cells(32, 20) + 1
    ElseIf Worksheets("MAPA").Cells(counter, 1).Interior.ColorIndex = 2 Then
        Worksheets("Skupinka 1").Cells(32, 20) = 0

   ' That's for one column, that was working,
   '  but when I try it for 2 columns, it was wrong

    ElseIf Worksheets("MAPA").Cells(counter, 2).Interior.ColorIndex = 5 Then
        Worksheets("Skupinka 1").Cells(32, 20) = Cells(32, 20) + 1
    ElseIf Worksheets("MAPA").Cells(counter, 2).Interior.ColorIndex = 2 Then
        Worksheets("Skupinka 1").Cells(32, 20) = 0

    End If
Next counter
End Sub

It shows me

Subscript out of range.

Answer 1

On this line and others there might be an issue:

Worksheets("Skupinka 1").Cells(32, 20) = Cells(32, 20) + 1

Left of the equation you have a absolute reference to a cell and on the right you have a relative. This could cause an issue if the wrong sheet is active.

Personally I use absolute references everywhere to prevent errors from wrong sheet being active.

Answer 2

I had the same problem you had. Then I added two worksheets, named "Mapa" and "Skupinka 1" to my Excel file, and it worked.

In your case, the "Subscript out of range" means that you are referring to a worksheet which does not exist, hence my observation.

If you are (almost) sure about the names of the worksheets, you might do the following: right-click on the sheet's name, choose "rename" and then copy the name of the worksheet, and follow this in in your VBA macro (sometimes you might think the name is correct, but you're missing a small typo).