• 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

    Hui...

  • 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.

68042
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 <<<
Code:
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
                          Else
                             Exit For
                      End If
   
          Next i
  Next j
Sheet3.Cells(i, j).Value = n 
End Sub
Thanks.
 
Last edited by a moderator:

AlanSidman

Active 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?
 

AlanSidman

Active 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.

Code:
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
68045
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.
 

vletm

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.
 

AlanSidman

Active 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.
 
Top