Little Help with formula for tracking days worked

[mikeym]

I need to track what days a person worked and the if they work 7 or more days in a row have it highlight those days ( say if I worked 9 days in row those cells would change to Red).
I will tracking this for a full year.

The formula I started using was =IF(ISNUMBER(MATCH("mike",C7:C18,0)),"Yes","No") Just could not figure out how to do the color change part.

Thanks for any help ..

 

[mikeym]

Hey I had a request to change my book and make the Dates going top to bottom and names across top. Can this be changed to still work?

 

[mikeym]

[TABLE="class: grid, width: 200, align: center"]
<tbody>[TR]
[TD][/TD]
[TD]mike
[/TD]
[/TR]
[TR]
[TD]1-1-16
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1-2-16
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Like this...just reverse what I had...(go figure)

 

[svendiamond]

Sub highlightDays()


Dim myRow As Long, myCol As Long, myCount As Integer
Dim lastRow As Long, lastCol As Long


lastRow = Cells(Rows.Count, 1).End(xlUp).Row
lastCol = Cells(1, Columns.Count).End(xlToLeft).Column


For myCol = 2 To lastCol
    myCount = 0
    For myRow = 2 To lastRow
        If Cells(myRow, myCol).Value = "Yes" Or Cells(myRow, myCol).Value = "yes" Then
            myCount = myCount + 1
        Else
            myCount = 0
        End If
        If myCount >= 7 Then
            Range(Cells(myRow - myCount + 1, myCol), Cells(myRow, myCol)).Interior.ColorIndex = 6
        End If
    Next myRow
Next myCol


End Sub

 

[mikeym]

Ok I was testing this and If I have say 14 "yes" If I replace a yes with a no It changes them all even if there is seven in a row still.

 

[svendiamond]

My last post didn't include the "clear highlights" line. Try it like this:

Sub highlightDays()


Dim myRow As Long, myCol As Long, myCount As Integer
Dim lastRow As Long, lastCol As Long


lastRow = Cells(Rows.Count, 1).End(xlUp).Row
lastCol = Cells(1, Columns.Count).End(xlToLeft).Column

Range(Cells(1,1),Cells(lastRow,lastCol)).Interior.ColorIndex = 0 'this resets the highlights

For myCol = 2 To lastCol
    myCount = 0
    For myRow = 2 To lastRow
        If Cells(myRow, myCol).Value = "Yes" Or Cells(myRow, myCol).Value = "yes" Then
            myCount = myCount + 1
        Else
            myCount = 0
        End If
        If myCount >= 7 Then
            Range(Cells(myRow - myCount + 1, myCol), Cells(myRow, myCol)).Interior.ColorIndex = 6
        End If
    Next myRow
Next myCol


End Sub