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.

Ref No	Date	Title
19/001	02-Feb	Administrator
19/002	09-Feb	Plumber
19/003	10-Feb	Administrator
19/004	26-Feb	Administrator
19/005	01-Mar	Builder
19/006	02-Mar	Baker
19/007	04-Mar	Engineer
19/008	08-Mar	Administrator
19/009	12-Mar	Administrator
19/010	15-Mar	Administrator
19/011	17-Mar	Builder
19/012	21-Mar	Plumber
19/013	02-Apr	Builder
19/014	03-Apr	Administrator
19/015	04-Apr	Engineer
19/016	04-Apr	Administrator
19/017	06-Apr	Administrator
19/018	07-Apr	Teacher
19/019	10-Apr	Accountant
19/020	11-Apr	Engineer
19/021	11-Apr	Engineer
19/022	11-Apr	Administrator
19/023	12-Apr	Teacher
19/024	15-Apr	Administrator
19/025	15-Apr	Administrator

<colgroup><col span="2"><col></colgroup><tbody>
</tbody>

 

[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!