Consecutive duplicates highlighting

[thedeans8486]

I am trying to keep tabs on employees that work 7 to 9 days straight. Supervisors put a W (the letter) for each day they work down Column C and if at any time they get 7 or 8 consecutive W's then those W's highlight yellow. Then if they get to 9 consecutive W's it highlights red. Is this possible?

 

[Joe4]

Welcome to the Board!

What exactly does the data look like (it might help if you can post a small sample). You cannot upload files to this site. But there are tools you can use to post screen images. They are listed in Section B of this link here: http://www.mrexcel.com/forum/board-a...forum-use.html. Also, there is a Test Here forum on this board that you can use to test out these tools to make sure they are working correctly before using them in your question.

Is each employee on their own page (tab), or is there some other column to indicate the employee?

 

[thedeans8486]

Thank for the welcome and thank you again for the helpful links I definitely will use. Their names are on one spreadsheet and are across row 3 starting at B3. Column A has dates down it starting at A4. If they worked they would put a W under their name across from the day they worked. I will read up on posting a sample right now.

 

[Joe4]

OK, this may work for you.

First step (for yellow):
Select cells C7 down to the end of your sheet.
Then select Conditional Formatting and enter this Conditional Formatting Formula:
=COUNTIF(C1:C7,"W")=7
And choose the yellow formatting option.

Second step (for red):
Select cells C9 down to the end of your sheet.
Then select Conditional Formatting and enter this Conditional Formatting Formula:
=COUNTIF(C1:C9,"W")=9
And choose the red formatting option.

 

[MickG]

Another Option:-
This code will work for any column (Except "A") from row 4 down, when the value is "W".
Consecutive count >6<9 = yellow, > 9 red.

To install code , Right click sheet tab, select "View Code" (from drop down,)
Vbwindow appears, Paste code into Vbwindow.
Close Vbwindow.

Code runs when a "W" is inserted in any column > "A"
when Consecutive cells count are >6, then cells are Highlighted.

Private [COLOR="Navy"]Sub[/COLOR] Worksheet_Change(ByVal Target [COLOR="Navy"]As[/COLOR] Range)
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, nRng [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Set[/COLOR] Rng = Range(Cells(4, Target.Column), Cells(Rows.Count, Target.Column).End(xlUp))
Rng.Interior.ColorIndex = xlNone
[COLOR="Navy"]If[/COLOR] Not Intersect(Target, Rng) [COLOR="Navy"]Is[/COLOR] Nothing And Target.Count = 1 [COLOR="Navy"]Then[/COLOR]
    [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
        [COLOR="Navy"]If[/COLOR] UCase(Dn.Value) = "W" [COLOR="Navy"]Then[/COLOR]
            [COLOR="Navy"]If[/COLOR] nRng [COLOR="Navy"]Is[/COLOR] Nothing [COLOR="Navy"]Then[/COLOR]
                [COLOR="Navy"]Set[/COLOR] nRng = Dn
            [COLOR="Navy"]Else[/COLOR]
                [COLOR="Navy"]Set[/COLOR] nRng = Union(nRng, Dn)
            [COLOR="Navy"]End[/COLOR] If
         [COLOR="Navy"]End[/COLOR] If
    [COLOR="Navy"]Next[/COLOR] Dn
    [COLOR="Navy"]If[/COLOR] Not nRng [COLOR="Navy"]Is[/COLOR] Nothing [COLOR="Navy"]Then[/COLOR]
        [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] nRng.Areas
            [COLOR="Navy"]If[/COLOR] Dn.Count > 6 And Dn.Count < 9 [COLOR="Navy"]Then[/COLOR]
                Dn.Interior.Color = vbYellow
            [COLOR="Navy"]ElseIf[/COLOR] Dn.Count >= 9 [COLOR="Navy"]Then[/COLOR]
                Dn.Interior.Color = vbRed
            [COLOR="Navy"]End[/COLOR] If
        [COLOR="Navy"]Next[/COLOR] Dn
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[thedeans8486]

Another Option:-
This code will work for any column (Except "A") from row 4 down, when the value is "W".
Consecutive count >6<9 = yellow, > 9 red.

To install code , Right click sheet tab, select "View Code" (from drop down,)
Vbwindow appears, Paste code into Vbwindow.
Close Vbwindow.

Code runs when a "W" is inserted in any column > "A"
when Consecutive cells count are >6, then cells are Highlighted.

Private [COLOR="Navy"]Sub[/COLOR] Worksheet_Change(ByVal Target [COLOR="Navy"]As[/COLOR] Range)
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, nRng [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Set[/COLOR] Rng = Range(Cells(4, Target.Column), Cells(Rows.Count, Target.Column).End(xlUp))
Rng.Interior.ColorIndex = xlNone
[COLOR="Navy"]If[/COLOR] Not Intersect(Target, Rng) [COLOR="Navy"]Is[/COLOR] Nothing And Target.Count = 1 [COLOR="Navy"]Then[/COLOR]
    [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
        [COLOR="Navy"]If[/COLOR] UCase(Dn.Value) = "W" [COLOR="Navy"]Then[/COLOR]
            [COLOR="Navy"]If[/COLOR] nRng [COLOR="Navy"]Is[/COLOR] Nothing [COLOR="Navy"]Then[/COLOR]
                [COLOR="Navy"]Set[/COLOR] nRng = Dn
            [COLOR="Navy"]Else[/COLOR]
                [COLOR="Navy"]Set[/COLOR] nRng = Union(nRng, Dn)
            [COLOR="Navy"]End[/COLOR] If
         [COLOR="Navy"]End[/COLOR] If
    [COLOR="Navy"]Next[/COLOR] Dn
    [COLOR="Navy"]If[/COLOR] Not nRng [COLOR="Navy"]Is[/COLOR] Nothing [COLOR="Navy"]Then[/COLOR]
        [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] nRng.Areas
            [COLOR="Navy"]If[/COLOR] Dn.Count > 6 And Dn.Count < 9 [COLOR="Navy"]Then[/COLOR]
                Dn.Interior.Color = vbYellow
            [COLOR="Navy"]ElseIf[/COLOR] Dn.Count >= 9 [COLOR="Navy"]Then[/COLOR]
                Dn.Interior.Color = vbRed
            [COLOR="Navy"]End[/COLOR] If
        [COLOR="Navy"]Next[/COLOR] Dn
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

MickG,

Absolutely love the code, is there any way to have the highlights if I delete W's. I put 9 straight w's together and it worked perfect but if I hit the delete button on the ones to bring the count down to not highlight it leave the highlight on the ones i deleted. Same with yellow.

 

[thedeans8486]

MickG,

MickG,

Absolutely love the code, I put 9 straight w's together and it worked perfect but if I hit the delete button on the ones to bring the count down to not highlight it leave the highlight on the ones i deleted. Same with yellow.

 

[MickG]

if your talking about selecting a number of cells then pressing delete. then the code will not run and the Highlighting will stay the same.
To overcome this try removing the bit of code below in red.

If Not Intersect(Target, Rng) Is Nothing[B][/B][COLOR="#FF0000"] And Target.Count = 1 [/COLOR]Then

 

[thedeans8486]

if your talking about selecting a number of cells then pressing delete. then the code will not run and the Highlighting will stay the same.
To overcome this try removing the bit of code below in red.

If Not Intersect(Target, Rng) Is Nothing[B][/B][COLOR="#FF0000"] And Target.Count = 1 [/COLOR]Then

I tried that and no it still leaves the highlight. Now if I put in a "O" for being off then it goes back. Can their be a code that defaults all selected cells to "O" so if I delete a "W" it reverts back to "O". Just wondering.. you guys are the best at this.

 

[Greg Truby]

If I might inquire, since I am curious... What characteristics of your data or processes made you determine that the VBA-based solution Mick offered was better than the Conditional-Formatting-based solution that Joe offered? I ask because, like Joe, I would have turned first to CF to solve the problem and would only have gone to a VBA solution if CF proved inadequate. How did Mick's solution outperform Joe's?