Counta function

[fluxcapacitr609]

Im trying to count the empty cells, but its counting empty cells that are colored. Any way to not count empty cells that have a color to them.

Sub percentPage()


Dim bcheck As Boolean
Dim myCell




On Error Resume Next


'January


bcheck = Evaluate("isref(Jan_2018!A1)")
If bcheck Then
    Sheets("Percentages").Range("B2").Formula = "=COUNTA(Jan_2018!A2:A500)"
    Sheets("Percentages").Range("C2").Formula = "=COUNTA(Jan_2018!B2:B500)"
End If






End Sub

 

[louisH]

Hi,

I think you'll have to loop through each cell in your range...

Sub Macro1()
    Dim i, j As Integer
    i = 0
    j = 0
    
    Dim ws As Worksheet
    Set ws = Worksheets("Percentages")
    
    For Each cel In Range("A2:A500")
    
        If cel.Interior.ColorIndex = xlNone And IsEmpty(cel) Then i = i + 1
        
    Next cel
    
    ws.Range("B2").Value = i
    
    For Each cel In Range("B2:B500")
    
        If cel.Interior.ColorIndex = xlNone And IsEmpty(cel) Then j = j + 1
        
    Next cel
    
    ws.Range("C2").Value = j
End Sub

 

[fluxcapacitr609]

Im sorry im trying to count cells that are not empty, but its counting empty cells because they are colored.

 

[louisH]

Ah yes my bad.
COUNTA isn't supposed to count cells if they juste have a color and Nothing in them...

Just add "= False" after IsEmpty(cel) like this :

Sub Macro1()
    Dim i, j As Integer
    i = 0
    j = 0
    
    Dim ws As Worksheet
    Set ws = Worksheets("Percentages")
    
    For Each cel In Range("A2:A500")
    
        If cel.Interior.ColorIndex = xlNone And IsEmpty(cel) = False Then i = i + 1
        
    Next cel
    
    ws.Range("B2").Value = i
    
    For Each cel In Range("B2:B500")
    
        If cel.Interior.ColorIndex = xlNone And IsEmpty(cel) = False Then j = j + 1
        
    Next cel
    
    ws.Range("C2").Value = j
End Sub