Determining value based on the next value

M

Mgmarm

New member
Joined
Feb 25, 2015
Messages
7
Reaction score
0
Points
0
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
    
    Range(Cells(a.Row, largest).Address).Select
    Selection.Interior.Color = RGB(0, 176, 240)
ElseIf a.Value <> 7 And a.Value = Application.Max(dayWeek) - 1 Then
    Range(Cells(a.Row, largest).Address).Select
    Selection.Interior.Color = RGB(0, 176, 240)
ElseIf a.Value <> 6 And a.Value = Application.Max(dayWeek) - 2 Then
    Range(Cells(a.Row, largest).Address).Select
    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,
 
Last edited by a moderator:
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
 
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
 
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
 
I cannot see any use of the Weekday function, and I don't really understand what you mean anyway, not the follow-up question.
 
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
Range(Cells(a.Row, vpwMark).Address).Select
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
 
Last edited:
Seeing your other post....

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
 
You must log in or register to reply here.
Back
Top