Little Help with formula for tracking days worked

mikeym

New Member
Joined
Jan 1, 2012
Messages
15
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 ..:)
 
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.
 
Upvote 0
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
 
Upvote 0
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:

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
 
Last edited:
Upvote 0
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.
 
Upvote 0
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 :)
 
Upvote 0

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top