Results 1 to 7 of 7

Thread: Determining value based on the next value

  1. #1

    Lightbulb Determining value based on the next value



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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 Bob Phillips; 2015-03-18 at 09:10 AM. Reason: Added code tags

  2. #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

  3. #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

  4. #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

  5. #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.

  6. #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
    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 by Mgmarm; 2015-03-18 at 04:30 PM.

  7. #7
    Magician NoS's Avatar
    Join Date
    Jan 2013
    Location
    British Columbia
    Posts
    751
    Articles
    0
    Excel Version
    Excel 2010 64bit
    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

Posting Permissions

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