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