• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User


  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Count None Color Cells

Hello, all Chandoo Experts.
Trying to count the Cells without color, on sheet 1 and the answer on sheet 3, the color in the sheet is a format color generate by another code.

here for example on I count before the color: meaning 3 values and the values 238, 176 do not count so the expected results are 3-8-8-8-8-8 do not show up on the picture but there are a color yellow on row 9 what I did is this:

>>> use code - tags <<<
Sub gg()
  Dim i As Long, n As Long, j As Long 
For j = 2 To 7
         For i = 3 To Range("I" & Rows.Count).End(3).Row
                     If Range("I" & i).Interior.ColorIndex = xlNone Then      
                       n = n + 1
                             Exit For
                      End If
          Next i
  Next j
Sheet3.Cells(i, j).Value = n 
End Sub
Last edited by a moderator:


Well-Known Member
In your original post, you show 2 cells with color, yet you claim the number of cells without color is 3. This confuses me. Are you counting cells that have been color filled or do not have color filled?


Well-Known Member
If you are interested in the number of cells in your file that don't have a yellow color then this code will do that. If you are looking for something else, please explain clearly what you want as your first explanation is not clear to me.

Option Explicit

Sub color()
    Dim c As Range, rng As Range
    Set rng = Range("I1").CurrentRegion
    Dim n As Long
    n = 0
    For Each c In rng
        If c.Interior.ColorIndex = -4142 Then
            n = n + 1
        End If
    Next c
    MsgBox ("NonColored Cells =" & n)

End Sub
AlanSidman, Sorry I am not clear in my post.
Please, let me explain: Yes I am interesting in the numbers of cells that don't have a yellow color then the results of each column will be display on sheet 3
in the file uploaded, sheet 1 have an array on I:N and the cells I3, I4 and I5 don't have a color so the number 3 I expect show up on sheet 3 B2
and so on, in other words MsgBox don't really help me, I am expecting the hole range in one time.
Thank you Sir.
I want to show you also this example with images
the numbers in the blue line 3 - 13 - 3 - 2 - 11 - 11 is the range I am expecting to see on sheet3("B2:G2") in this images, is possible to see my intention, count-down the numbers from row 3, in this array ("I:N").
and again thank you Sir.
I really appreciate your time.
vietm, thank you, I try to provide the necessary info, in order to receive help, and this color are generated by a code, if I am wrong about the name, sorry, I just trying after the first code run, play the second one, that is the one I would like some help. Please, thank you vietn.


Excel Ninja
vicktor schausberger
If You use 'conditional formatting' then You'll have there code to get eg some colors ... eg yellow - okay.
BUT color of cell can be different!
You code tries to check cells .interior.colorindex -value ... not 'conditional formatting color'.
For me, those 'conditional formatting colors' are like 'small pieces of paper' over page.


Well-Known Member
Because you are using Conditional Formatting, it will be necessary to use the same logic that you applied for the conditional formatting to determine which cells are colored and not colored. They will not be found using the .interior.colorindex value.