Highlighting every 6th occurence in a table using conditional formatting

[PIRC13]

I have a spreadsheet which is used to add in the vacancies which are closed. As you can see there is a reference number, a date and the title of the vacancy. I want to highlight every 6th occurence of the Administrator vacancy for the records to be audited. I want all fields in the record to be highlighted in a colour, however the importance is that although these are added in no particular sequence, every sixth record for administrator, no matter the date, are to be highlighted.

[TABLE="width: 254"]
<colgroup><col span="2"><col></colgroup><tbody>[TR]
[TD]Ref No[/TD]
[TD]Date[/TD]
[TD]Title[/TD]
[/TR]
[TR]
[TD]19/001[/TD]
[TD]02-Feb[/TD]
[TD]Administrator[/TD]
[/TR]
[TR]
[TD]19/002[/TD]
[TD]09-Feb[/TD]
[TD]Plumber[/TD]
[/TR]
[TR]
[TD]19/003[/TD]
[TD]10-Feb[/TD]
[TD]Administrator[/TD]
[/TR]
[TR]
[TD]19/004[/TD]
[TD]26-Feb[/TD]
[TD]Administrator[/TD]
[/TR]
[TR]
[TD]19/005[/TD]
[TD]01-Mar[/TD]
[TD]Builder[/TD]
[/TR]
[TR]
[TD]19/006[/TD]
[TD]02-Mar[/TD]
[TD]Baker[/TD]
[/TR]
[TR]
[TD]19/007[/TD]
[TD]04-Mar[/TD]
[TD]Engineer[/TD]
[/TR]
[TR]
[TD]19/008[/TD]
[TD]08-Mar[/TD]
[TD]Administrator[/TD]
[/TR]
[TR]
[TD]19/009[/TD]
[TD]12-Mar[/TD]
[TD]Administrator[/TD]
[/TR]
[TR]
[TD]19/010[/TD]
[TD]15-Mar[/TD]
[TD]Administrator[/TD]
[/TR]
[TR]
[TD]19/011[/TD]
[TD]17-Mar[/TD]
[TD]Builder[/TD]
[/TR]
[TR]
[TD]19/012[/TD]
[TD]21-Mar[/TD]
[TD]Plumber[/TD]
[/TR]
[TR]
[TD]19/013[/TD]
[TD]02-Apr[/TD]
[TD]Builder[/TD]
[/TR]
[TR]
[TD]19/014[/TD]
[TD]03-Apr[/TD]
[TD]Administrator[/TD]
[/TR]
[TR]
[TD]19/015[/TD]
[TD]04-Apr[/TD]
[TD]Engineer[/TD]
[/TR]
[TR]
[TD]19/016[/TD]
[TD]04-Apr[/TD]
[TD]Administrator[/TD]
[/TR]
[TR]
[TD]19/017[/TD]
[TD]06-Apr[/TD]
[TD]Administrator[/TD]
[/TR]
[TR]
[TD]19/018[/TD]
[TD]07-Apr[/TD]
[TD]Teacher[/TD]
[/TR]
[TR]
[TD]19/019[/TD]
[TD]10-Apr[/TD]
[TD]Accountant[/TD]
[/TR]
[TR]
[TD]19/020[/TD]
[TD]11-Apr[/TD]
[TD]Engineer[/TD]
[/TR]
[TR]
[TD]19/021[/TD]
[TD]11-Apr[/TD]
[TD]Engineer[/TD]
[/TR]
[TR]
[TD]19/022[/TD]
[TD]11-Apr[/TD]
[TD]Administrator[/TD]
[/TR]
[TR]
[TD]19/023[/TD]
[TD]12-Apr[/TD]
[TD]Teacher[/TD]
[/TR]
[TR]
[TD]19/024[/TD]
[TD]15-Apr[/TD]
[TD]Administrator[/TD]
[/TR]
[TR]
[TD]19/025[/TD]
[TD]15-Apr[/TD]
[TD]Administrator[/TD]
[/TR]
</tbody>[/TABLE]

 

[Marcelo Branco]

Select all data (not the headers), say, A2:C100

In Conditional Formatting use this formula
=AND($C2="Administrator",MOD(COUNTIF($C$2:$C2,$C2),6)=0)

Pick the format you want

M.

 

[tyija1995]

Hey,

I know you can do it via VBA if that helps:

Sub HighlightSixthAdmin()
    Dim txt As String
    Dim incrementor As Integer
    Dim rng As Range
    Dim cell As Range
    Set rng = Range("Table1[Title]")
    txt = "Administrator"
        For Each cell In rng
            If cell.Value = txt Then
                incrementor = incrementor + 1
                    If incrementor Mod 6 = 0 Then
                        cell.Interior.ColorIndex = 36
                    End If
            End If
        Next cell
End Sub

I'll look in to doing it with CF though!