Count if - cell color index & cell value (range of value)

[PENGUIN]

Hi Guys,

I have an spreadsheet that presents the unloading of the vechicles on the unloading doors.
I need to have the formula/macro line that shows live' doors stats.

Basicilaly there are two ranges of the unloading doors: 1st- doors from 301 to 329, 2nd - doors from 201 to 227.
Whatever is on the doors is highlighted yellow and have an remark with the doors number, for eg. 315.
There are other colors used for: parking bay, unloaded, awaiting driver etc. We only focus on yellow please.

Now I need the excel to show me the number of the yellow lines that have range from 301 - 329 and another range of 201 - 227.
Both lot of data to indicate how many vechicles is currently on the 200's doors and how many vechicles is currently on the 300's doors.
I hope I have explained this fine and you can get the idea of what I actually need.

Please see attached EXAMPLE simple form to show visualy.
*Please note there is an about 90 rows like this and these change houndreds times during the hour.*

[Szaraq]

Hi, what would you tell about something like that:


Sub macro()


Dim cell200 As Range, cell300 As Range, i As Integer, count200 As Integer, count300 As Integer

Set cell200 = Range("C10")
Set cell300 = Range("C11")
count200 = 0
count300 = 0

For i = 1 To Range("C1").End(xlDown).Row
With Cells(i, 3)
If .Interior.ColorIndex = 6 Then
If .Value > 300 And .Value < 329 Then count300 = count300 + 1
If .Value > 200 And .Value < 227 Then count200 = count200 + 1
End If
End With
Next

cell200 = count200
cell300 = count300


End Sub


The only thing is that you have to run this procedure manually. I don't know the conditions when would you like to make the calculations. You should for example put this code into "Workbook_Open" section if you want to run this macro every time the user opens this workbook.

You should also change Set cell200 = Range("C10") and Set cell300 = Range("C11") change into your own cell addresses, where you want to put the calculations.

Hope it helped.

[PENGUIN]

Hi,
This is exacly what I were looking for. Thank you.
DZIEKUJE :)

[Szaraq]

You're welcome. Glad could help :).

[PENGUIN]

Hi,
Please see below.
As in the PM it doeas not work on this file? Any ideas?

[Kenneth Hobs]

Try to keep example files simple so we can more easily target a solution. You have 5 sheets. I don't know what columns in those are the ones to check. I don't see any yellow lines. Try adding a file where you enter some data and then manually mark what you need to point it out. You can add comments. You can manually count the sum of what you need and point that out as well. Providing a detailed example like this will help us help you best.

To target fill colors best, use manually marked fills rather than conditional format fills.

For both the case above and the general solution, a Change event on the sheet should suffice. Search the forum for the word "Intersect" for example code.