Using Countif in VBA
 

Ok, the code below was given to me by a user of the forums that wa
helping me solve a problem a while ago(thanks Frank :) ). With thi
code, for everysheet in the workbook, it looks at the range H1:H275 an
counts how many cells in that range have the current date. The fina
count is given in a message box.

Private Sub Counting_Test()
Dim wks as worksheet
Dim count As Single

count = 0
for each wks in activeworkbook.worksheets
count = count + application.countif(wks.range("H1:H275"),Date)
next
MsgBox count
End Sub

This works perfectly fine but now, i need to kick it up a notch (
tried myself but nothing was working): I need to code it so that i
looks thru the same range but this time counts the cells that have th
current date ONLY if the cell has a certain background color (fo
example, red).

I also need another version that looks thru the same range and look
for cells with today's date, but this time only counts the cell only i
the Offset(0, -4) cell value is "PICK UP"

I know i may be asking for a lot but I need help badly...the boss i
yelling at me about these reports not being done, and I need this t
help compile info for me and complete these reports.

Thanks in advance for you help :

--
Message posted from http://www.ExcelForum.com

Using Countif in VBA
 

Here's a start for the color problem

Private Sub Counting_Test1()
Dim wks as worksheet
dim r as range
Dim count As Single

count = 0
for each wks in activeworkbook.worksheets
for each r in wks.Range("H1:H275")
if r.Value = Date And r.Interior.ColorIndex = 3 Then
count = count + 1
end if
next
next
MsgBox count
End Sub

The offset problem should be similar (assuming you are looking at
rows above):

Private Sub Counting_Test2()
Dim wks as worksheet
dim r as range
Dim count As Single

count = 0
for each wks in activeworkbook.worksheets
for each r in wks.Range("H1:H275")
if r.Value = Date And wks.Range("H" & r.Row-4).Value = "PICK UP
Then
count = count + 1
end if
next
next
MsgBox count
End Sub




--
Message posted from http://www.ExcelForum.com