# Thread: Determining value based on the next value

1. ## Determining value based on the next value

Hi guys,

I have an array showing numbers 1 to 7 I am having vba check a column for the max number for the week and filling them with color..
The problem I am having here is that I am using an if and elseif statements.. If 7 is not existent mark 6 else is 6 is not existent so on..
While looping through the column excel is marking 5 and 6 when I just wanted the highest value marked from 1 to 7

Code:
```Set aCell = ActiveSheet.UsedRange.Find(What:="VPW", LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)

dayWeek = Array(1, 2, 3, 4, 5, 6, 7)
largest = aCell.Column
For Each a In Intersect(Range("N:N"), ActiveSheet.UsedRange)
For mcount = 1 To 7
If a.Value = Application.Max(dayWeek) Then

Selection.Interior.Color = RGB(0, 176, 240)
ElseIf a.Value <> 7 And a.Value = Application.Max(dayWeek) - 1 Then
Selection.Interior.Color = RGB(0, 176, 240)
ElseIf a.Value <> 6 And a.Value = Application.Max(dayWeek) - 2 Then
Selection.Interior.Color = RGB(0, 176, 240)

End If
Exit For
Exit For```
Sorry I know I am new and this probably looks like real bad coding... = (

Any help would be appreciated

Thanks,  Reply With Quote

2. I see a number of oddities,
- two Exit For's at the end - should they be Next x?
- the mcount loop with the IF seems redundant
- testing a reducing dayWeek looks problemmatical

Not really sure what you are trying to do, but is this any better?

Code:
```    With ActiveSheet
Set aCell = .UsedRange.Find(What:="VPW", _
LookIn:=xlValues, _
LookAt:=xlWhole)

largest = aCell.Column
For Each a In Intersect(.Range("N:N"), .UsedRange)

If a.Value >= 1 And a / Value <= 7 Then

.Cells(a.Row, largest).Interior.Color = RGB(0, 176, 240)
End If
Next a
End With```  Reply With Quote

3. Hi Bob,

Thanks for the reply the code looks a lot cleaner
I am using the WEEKDAY function (1 - 7) for a whole month
What I am trying to do is loop 1 to 7 and color in the highest value..
and to reiterate when 1 or Monday  Reply With Quote

4. Also If you don't mind me asking.. in the for each loop is it possible to use "largest" credentials as the column it Intersects.. Instead of physically typing in the column

Thanks again,

Matt  Reply With Quote

5. I cannot see any use of the Weekday function, and I don't really understand what you mean anyway, not the follow-up question.  Reply With Quote

6. oo im sorry let me list the code

Set aCell = ActiveSheet.UsedRange.Find(What:="VPW", LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)

vpwMark = aCell.Column
aVpwMark = vpwMark - 1

For Each a In Intersect(Range("A:A"), ActiveSheet.UsedRange)
For mcount = 1 To 100
If a.Value Like "*/2015" Then
Selection.FormulaR1C1 = "=WEEKDAY(RC[-" & aVpwMark & "],2)"

End If

Exit For
Exit For

Next
Next

I had to run it from A column because the dates were listed there .. I hope this helps.. sorry about the bad code I'm still new. >.<
Again thanks a lot I appreciate it  Reply With Quote

Assuming days start at A2 and numbers without square brackets in column B

Is this something you could build on ?
Code:
```Sub IdentifyLastDayWorkedInWeek()
Dim rng As Range
Dim cel As Range
Dim LastRow As Long

LastRow = Sheet1.Cells(Rows.Count, "B").End(xlUp).Row
Set rng = Sheet1.Range("B2:B" & LastRow)

For Each cel In rng
If cel.Value > cel.Offset(1, 0).Value Then
cel.Interior.Color = RGB(0, 176, 245)
End If
Next cel

End Sub```  Reply With Quote

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•