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

 

[svendiamond]

Use conditional formatting. In what column are the days that you want to highlight?

 

[mikeym]

Conditional Would not do what I need I don't think. I am pulling the data from another sheet and I will be highlighting multiple columns for different People.
I think I just really need help getting it to change cell colors when the person work 7 or more days in a row.
Thanks fro trying to help me

 

[svendiamond]

A formula is not going to change the color of cells. Conditional formatting will, based on whatever you want. What column do you want to highlight? What column are the dates in? What does your data look like?

 

[mikeym]

Just simple Names in column A and dates going across top, If the mike worked on Jan 1st I would have Yes in cell b2...Make sense?

	Jan 1	Jan 2
Mike	Yes	No

 

[svendiamond]

Interesting! Save a back-up of your workbook, then try this code:

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


End Sub

 

[mikeym]

Didn't see a Change...I made up a version of my workbook here and tried it tonight

 

[svendiamond]

Can you post a larger sample of your data? I made a sheet just like the example you posted and it worked for me. To make it easier to copy and paste your data into the forum, please consider installing the Mr Excel HTML Maker from this link: http://www.mrexcel.com/forum/about-board/508133-attachments.html#post2545970

 

[Dryver14]

Is it possible the O/P is typing "yes" not "Yes", this will not see "yes"

 

[svendiamond]

Ah, good point. How about this fix:

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


End Sub