vba: count non colored

[montecarlo2012]

Hello Mr. Excel Experts.

Sub Basic_Programming()
Dim I As Integer, k As Integer
lastrow = Cells(Rows.Count, "B").End(xlUp).Row

I = 4  '---------------------------------------------------------------[start Cell B4]
If Cells(I, "B").Value = vbYellow Then '-----------------[count until yellow is found]
k = Cells(Rows.Count, "B").End(xlUp).Row
End If
For k = 4 To lastrow
        k = k + 1
Next k
Range("B1").Value = k
End Sub

This is a Basic Idea, I tried, and get wrong answer. I want to count only the cells without color,
I fill the color manually, no formatting, the values don't go by any pattern, and look for a code

I was expecting 5 as an answer, but I got 12, I am trying to keep AS SIMPLE OR BASIC AS POSSIBLE,
Thanks.

 

[DanteAmor]

Thanks Rick, for your support, you are always welcome with your knowledge.
I hope everything is good with you and your family.

My code works fine, but you must manually fill the cell with a color. And you must start the cycle in row 2

Sub DanteAmor1()
  Dim i As Long, n As Long
  For i = 2 To Range("I" & Rows.Count).End(3).Row
    If Range("I" & i).Interior.ColorIndex = xlNone Then n = n + 1 Else Exit For
  Next
  Range("I1").Value = n - 1
End Sub

 

[montecarlo2012]

Rick Rothstein
Thank you so much.
I tried and the result are not the expected values.

 

[montecarlo2012]

DanteAmor
Thank you.
I got 2716 as an answer, no 3 as expected.
maybe because the color as you said, is a formatting.
Thank you. DanteAmor.

 

[Rick Rothstein]

@montecarlo2012,
Not much for me to go on there. Any chance you can give more details? Posting a copy of your workbook book to a file-sharing site (DropBox is safe and well-accepted by all) so we can see exactly what you are working with would be real helpful.

@Dante,
Scary times for sure, but I and my family are safe and well... hopefully we can stay that way in the future. Hopefully the same applies to you and your family as well.

 

[montecarlo2012]

I really appreciate your invitation I have my workbook here:

Google Sheets - create and edit spreadsheets online, for free.

Create a new spreadsheet and edit with others at the same time -- from your computer, phone or tablet. Get stuff done with or without an internet connection. Use Sheets to edit Excel files. Free from Google.

docs.google.com

google spreadsheet,
also what I can say here is, the project it is not big, so right here right now with some images and all that knowledge you have guys, would be not a issue. so here we go: [images]

and like this go until ("GU") only arrays with all those formatting yellow marks, so right on top I am trying to see how many cells are before the color, that why I ask about the basic code so maybe I will come out with something. and this workbook have only the code that generate all this arrays, that's it.
thank you for asking.

[ we can see exactly what you are working with]

 

[Sulprobil]

Why bother with color codes?

Just fill a reference cell with the (non-)color you want to count and compare to that value:

Function sbCountMyColor(r As Range)
'Counts all cells of r which have the same color as calling
'cell (where this function is called from).
'Please keep in mind that this function is NOT automatically
'updated if an input cell format changes because that does
'not trigger a recalculation event! To ensure an up-to-date
'calculation you need to do this yourself
'(with CTRL + ALT + F9, for example).
Dim v

For Each v In r
    If v.Interior.Color = Application.Caller.Interior.Color Then
        sbCountMyColor = sbCountMyColor + 1
    End If
Next v

End Function

Please note that changing a cell color not necessarily triggers a recalculation.

You might want to add a worksheet change event (define a name ColoredRange which defines the region of potential colored cells):

Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Range("ColoredRange").DirectDependents.Calculate
End Sub

Do not forget to apply a sum check for all cells in question so that you do not miss a color "outlier". Cell total minus non-colored cells minus sum of all different colors (cells) should be zero.

 

[montecarlo2012]

Sulprobil. I really appreciate your input Sir.
Results, nothing right come out from the code.

 

[Sulprobil]

montecarlo2012,

I really appreciate your output
My help cannot be better than your hints on what exactly you tried and what exactly happened, I am afraid.

 

[Sulprobil]

If you press ALT + F11, insert a new module, copy the sbCountMyColor code in it, then go back to the worksheet, then you can paint a cell A1 yellow and array-enter into A1:
=sbCountMyColor(A2:Z99)
and you will get the count of how many cells in A2:Z99 are yellow.

 

[montecarlo2012]

Sulprobil. Thanks.

Sorry IF sounds like a, I expresses indirectly what I prefers not to say explicitly, Sorry about that.
Meaning for me is that you really want to help me.
I really appreciate that, So for that reason Let me show you all the posting and troubles I have been;
just trying to solve this “hobby-project”,
so you can Have a very direct contact not indirectly,
This part of my journey, start here, Sir:

Great and Smart experts help me here but still. Sorry.
the post you see today is the result of all of this:

VBA LOOPING FORMULA TREND
vba, countif in a loop.
Loop is not returning nothing
vba: count non colored

beside I have been reading books, websites
Thank you Sir.