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?

 

[thedeans8486]

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?

Just a personal preference I actually use CF alot but in this case I just choose VBA

 

[Joe4]

Just a personal preference I actually use CF alot but in this case I just choose VBA

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.

But the CF option handles that situation that you mention automatically without having to do anything special.
So why not use that option? Why re-create the wheel if there is already functionality that exists that does what you want?

 

[thedeans8486]

But the CF option handles that situation that you mention automatically without having to do anything special.
So why not use that option? Why re-create the wheel if there is already functionality that exists that does what you want?

Just personal preference

 

[Joe4]

Well, given that the other option is not working for you, why not try the other options and see if that does what you want?

 

[thedeans8486]

Well, given that the other option is not working for you, why not try the other options and see if that does what you want?

I did and the problem with it is it highlights the first w in the line of consecutive w's not all 7 or 8 or 9.

 

[Joe4]

I did and the problem with it is it highlights the first w in the line of consecutive w's not all 7 or 8 or 9.

OK, I hadn't seen that feedback provided previously. I wasn't sure if you only wanted the last ones highlighted (7,8, and 9), or all of them.
Note, if you apply the CF like I described, if would highlight the last ones (7,8,9), and not the first ones. It sounds like you went the wrong way in your range setting in the formula.
But it sounds like that is not what you want anyway...

 

[barry houdini]

If you want to format all 7 or 8 W's in yellow (or all 9 in red) then you can do that this way using conditional formatting.

Assuming data range is B4:B100 then select that range and apply this formula in CF

=SUM((IFERROR(COUNTIF(OFFSET(B4,ROW(INDIRECT("1:7"))-7,0,7,1),"w"),0)=7)+0)

apply yellow fill

then use this formula for second condition

=SUM((IFERROR(COUNTIF(OFFSET(B4,ROW(INDIRECT("1:9"))-9,0,9,1),"w"),0)=9)+0)

apply red fill

If you apply the conditions in that order then the red condition should be first, which is what you need. If they aren't in that order then swap them round

 

[MickG]

I'm sorry I gave you an Option based on code in another similar Thread. (Getting a bit confused in old age !!!!)
I will have to have another look !!!

 

[MickG]

Try this:-

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, nnRng [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Set[/COLOR] Rng = Range(Cells(4, Target.Column), Cells(Rows.Count, Target.Column).End(xlUp))
[COLOR="Navy"]If[/COLOR] Not Intersect(Target, Rng) [COLOR="Navy"]Is[/COLOR] Nothing [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"]Select[/COLOR] [COLOR="Navy"]Case[/COLOR] Dn.Count
               [COLOR="Navy"]Case[/COLOR] [COLOR="Navy"]Is[/COLOR] < 7: Dn.Interior.ColorIndex = xlNone
               [COLOR="Navy"]Case[/COLOR] [COLOR="Navy"]Is[/COLOR] >= 9: Dn.Interior.Color = vbRed
               [COLOR="Navy"]Case[/COLOR] [COLOR="Navy"]Is[/COLOR] > 6, [COLOR="Navy"]Is[/COLOR] < 9: Dn.Interior.Color = vbYellow
            [COLOR="Navy"]End[/COLOR] Select
        [COLOR="Navy"]Next[/COLOR] Dn
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]End[/COLOR] If
Rng.SpecialCells(xlCellTypeBlanks).Interior.ColorIndex = xlNone
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick