Highlight a run of values

[LGMan]

Hi, I'm trying to highlight absence indicators within a range of 42 columns wide, which equates to 6 weeks of attendance.
The absence abbrevetions are 'R', 'F' and 'L'

What I need to try and do is highlight these values if they are in a sequence (regardless if it's a mixture of the values) of at least 5 adjacent cells. When I say highlight, I mean change the cell fill colour to 49407 (orange)
If it makes it easier, I could perform a global replace and make my interested values all as say 'A' for Absent.

Now, if everyone took thier leave in blocks of 5 days, Mon to Fri, I'd be able to accomplish it

 

[Gavona]

If the only values are the three you mention then a conditional format using a formula like this would do it:
=COUNTA(A7:E7)=5
If there are other abbreviations that you don't want counted then a series of CountIFs would do it (again within conditional format).

No need for VBA

Gavin

 

[SkipVought]

I agree that Conditional Formatting is the way to go.

However, the formula to accomplish is kind of hairy and so here's a half-way compromise, using a UDF for the formula and then using it in the CF...

Function CountFRL(rng As Range) As Integer
    Dim i As Integer, j As Integer, iSUM As Integer
    
    For i = -4 To 0
        iSUM = 0
        
        For j = 0 To 4
            Select Case Cells(rng.Row, rng.Column).Offset(0, i + j).Value
                Case "F", "R", "L"
                    iSUM = iSUM + 1
            End Select
            If iSUM = 5 Then
                CountFRL = 1
                Exit Function
            End If
        Next
        
    Next
End Function

In your CF...
[tt]
=CountFRL(E2)=1
[/tt]
where E2 is the UPPER Left-Hand cell in the CF selection.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

Sorry, I posted somewhat of a Rube Goldberg solution. Please replace with...

Function CountFRL(rng As Range) As Integer
    Dim i As Integer, j As Integer, iSUM As Integer
    
    For i = -4 To 0
        iSUM = 0
        
        For j = 0 To 4
            Select Case rng.Offset(0, i + j).Value
                Case "F", "R", "L"
                    iSUM = iSUM + 1
            End Select
            If iSUM = 5 Then
                CountFRL = 1
                Exit Function
            End If
        Next
        
    Next
End Function

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[LGMan]

Thanks, as I've got a vba proceedure to get the data into shape, I thought some extra vba would be a tidy solution.

There are indeed many other values that I'm not interested in, which is why I wanted to highlight the ones I'm after to make a quick and easy kind of visual picture.
The problem with Countifs, is the guy that usually covers for me when I'm absent has an older version of Excel (pre countifs) as I've come across that problem before.

 

[LGMan]

Sorry guys, i didn't see Skips replies.
Skip, your solution works a treat, again many thanks for your time and expertise. Thanks to Gavin as well.

 

[Gavona]

The formula for CF would be something like (in E4): =COUNTIF(A4:E4,"F")+COUNTIF(A4:E4,"R")+COUNTIF(A4:E4,"L")=5

No reason why it or Skip's version shouldn't be applied by code.

Gavin

 

[SkipVought]

Gavin,

For column E EITHER
[tt]
A4:E4 or B4:F4 or C4:G4 or D4:H4 or E4:I4
[/tt]
and equal 5, according to my understanding.


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}