Excel VBA need help with loop 1

[access101]

I am looking for a way to loop through records on tab1 and color fill on tab2. This code below works perfectly however i could have to write out a thousand more lines to finish it and i know there must be a simple loop which will solve it so any help will be greatly appreciated.

If Sheet1.Range("k5") = TimeValue("7:00") And Sheet1.Range("l5") = TimeValue("1:30p") Then
Sheet2.Range("(b90)n90)").Interior.ColorIndex = 46
End If
If Sheet1.Range("k6") = TimeValue("7:00") And Sheet1.Range("l6") = TimeValue("1:30p") Then
Sheet2.Range("(b91)n91)").Interior.ColorIndex = 46
End If

etc....

 

[SkipVought]

Hi,

You have given no specific information regarding your loop. So generally...

dim r as range

for each r in range(Sheet1.cells(2, 1), Sheet1.cells(2,1).end(xldown))
  debug.print r.value
next

for all values in column A displayed in the immediate Window

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[xlbo]

For i = 5 to EndNumber

If Sheet1.Range("k" & i) = TimeValue("7:00") And Sheet1.Range("l" & i) = TimeValue("1:30p") Then
Sheet2.Range("b" & 85 + i & ":n" & 85 + i).Interior.ColorIndex = 46
End If

Next i

Just define your endnumber as the last row you want to check

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[access101]

thanks for the code it works great!