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]

Ok I'll try this one

 

[mikeym]

Ok This one worked. Is there anyway I can make this constantly run? Example If I seen I was flagged for working 7 days I could remove my name and it would undo the highlight? This is first time I have put script into a sheet like this I might be doing it wrong.

 

[Dryver14]

I think you would need to highlight the cells then select no fill in the fill colour on the home tab, I am guessing the reason for this is that someone
should not work more days than this in a row for company/legal reasons. even if you went back and changed a yes to no you would still need to manually change the colours

 

[svendiamond]

To make it automatically run and undo highlights that are no longer seven "yes" values in a row, right-click on the sheet name and click "View Code" -- then enter this code:

Private Sub Worksheet_Change(ByVal Target As Range) 'this makes the code run any time a cell is changed


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 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 >= 7 Then 'changed this to seven sorry about that
            Range(Cells(myRow, myCol), Cells(myRow, myCol - myCount + 1)).Interior.ColorIndex = 6
        End If
    Next myCol
Next myRow


End Sub

 

[mikeym]

No It still acts the same. I change data in cells and it didn't undo the color change. I reset the colors back to white and entered data then go to VB Editor panel and run the Code and it changes the color then but acts like it just runs once.

 

[Dryver14]

This works perfectly for me, are you placing this code in the worksheet module not a normal module

 

[mikeym]

Yea I put mine in Worksheet

 

[mikeym]

Yea I put mine in Worksheet

If it's supposed to be in a module it doesn't work until it's in the sheet...Sorry It might just be me where I have not worked with this type of code before.

 

[Dryver14]

Have a look here

http://jlathamsite.com/Teach/WorksheetCode.htm

 

[mikeym]

Ok sorry it took so long to reply, I got it to work on my workbook copy I had at home so must be something with my work PC???? It does work GREAT thanks so much.

Thanks you guys for the Help, I know it's hard explaining things to someone that's not a expert like you guys