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]

How about

Sub text()
  Dim i As Long, n As Long, m
  For i = 4 To Range("B" & Rows.Count).End(3).Row
    If Range("B" & i).Interior.ColorIndex = xlNone Then
      n = n + 1
    End If
  Next
  Range("B1").Value = n
End Sub

 

[montecarlo2012]

How about

Sub text()
  Dim i As Long, n As Long, m
  For i = 4 To Range("B" & Rows.Count).End(3).Row
    If Range("B" & i).Interior.ColorIndex = xlNone Then
      n = n + 1
    End If
  Next
  Range("B1").Value = n
End Sub

thank you Sir for your help, I run the code and the answer from your code is 6, what I expect is 5, meaning count the cells before the color.
I really appreciate your Input.

 

[DanteAmor]

So when it gets to yellow, then stop counting?

Try this

Sub text()
  Dim i As Long, n As Long, m
  For i = 4 To Range("B" & Rows.Count).End(3).Row
    If Range("B" & i).Interior.ColorIndex = xlNone Then
      n = n + 1
    Else
      Exit For
    End If
  Next
  Range("B1").Value = n
End Sub

 

[montecarlo2012]

Excellent Work Mr. DanteAmor, Thank you so much, You really help exactly in the way I need.
Be safe.
You are GREAT.

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.

 

[montecarlo2012]

Sir, Do you mind a little extension on this question, PLEASE.

 

[DanteAmor]

Sir, Do you mind a little extension on this question, PLEASE.

Of course, if I can help you with pleasure I will.

 

[montecarlo2012]

Thanks, You are very kind.
I try to use the same code in other workbook, I change what supposedly has to be change, like column number, etc, but I got an answer but wrong, maybe because the color in this case is generate by another code, and is a formatting?, this is what I tried.

Sub DanteAmor1()
  Dim i As Long, n As Long, m
  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
  Range("I1").Value = n - 1
End Sub

and this is the spreadsheet I am trying to work on

as you see on "I1" I got 20709 and of course is wrong because what I was expecting is 3, meaning count 238 as one, 8 is two, other 8 is three and 10 right before yellow is 4 by I change the line
Range*"I").Value = n - 1 so the results has to be 3
Thank you Dante.

 

[Rick Rothstein]

Here is another macro that you can use (no loops) to answer your first question and which you should be able to modify for your second question...

Sub FirstNotYellowCount()
  Application.FindFormat.Clear
  Application.FindFormat.Interior.Color = vbYellow
  Range("B1").Value = Columns("B").Find("", SearchFormat:=True).Row - 4
  Application.FindFormat.Clear
End Sub

Note 1: The 4 is the row number for first cell of the range you want to search.
Note 2: The color the code is looking for is the standard yellow color.