Highlight a cell only when a value has appeared 5 times

[tjp500]

Hi,

Is it possible to have a cell highlighted but only when a value above it as appeared for the 5th time. As per the image I want to be able to have P1 highlighted but only after it as appeared 5 times,
and then for the sequence to carry on down the column as more data gets added. Is it also possible to apply this to more data in the same column ( I.E P3 ) in the image, at the same time ?

Any help would be much appreciated.

Thank you

 

[Fluff]

Hi & welcome to MrExcel.
How about

+Fluff 1.xlsm
	K
1	P1
2	P3
3	P4
4	P3
5	P1
6	P5
7	P4
8	P9
9	P1
10	P5
11	P6
12	P1
13	P3
14	P1
15	P2
16	P3
17	P1
18	P2
19	P3
20	P1
21	P1
22	P2
23	P1
24	P3
25	P1
26
27
28
29
30
31
Main

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
K1:K37	Expression	=AND(K1<>"",MOD(COUNTIF(K$1:K1,K1),5)=0)	text	NO

 

[tjp500]

Hi & welcome to MrExcel.
How about

+Fluff 1.xlsm
	K
1	P1
2	P3
3	P4
4	P3
5	P1
6	P5
7	P4
8	P9
9	P1
10	P5
11	P6
12	P1
13	P3
14	P1
15	P2
16	P3
17	P1
18	P2
19	P3
20	P1
21	P1
22	P2
23	P1
24	P3
25	P1
26
27
28
29
30
31
Main

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
K1:K37	Expression	=AND(K1<>"",MOD(COUNTIF(K$1:K1,K1),5)=0)	text	NO

Thank you very much that worked a treat

 

[etaf]

EDIT - Ignore
Posted as i was composing the sheet
you could use
=AND(A11<>"",MOD(COUNTIF($A$11:$A11,A11),5)=0)
BUT that would colour them the same colour

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[kanadaaa]

You could also do it with VBA (works only if the first data is in row 2)

Sub HighlightEach5th()
    Dim lastRow As Long, i As Long, cnt As Long, rng As Range, tempRng As Range
    lastRow = Range("P" & Rows.Count).End(xlUp).Row
    Application.ScreenUpdating = False
    For i = 2 To lastRow
        Set rng = Range("P1:P" & lastRow).Find(Cells(i, "P").Value, , xlValues, xlPart, xlByRows, xlNext)
        cnt = 1
        Set tempRng = rng
        Do While Not rng Is Nothing
            Set rng = Range("P1:P" & lastRow).FindNext(rng)
            If rng.Address = tempRng.Address Then
                Exit Do
            End If
            cnt = cnt + 1
            If cnt Mod 5 = 0 Then
                rng.Interior.Color = vbYellow
            End If
        Loop
    Next i
    Application.ScreenUpdating = True
End Sub

 

[tjp500]

Hi & welcome to MrExcel.
How about

+Fluff 1.xlsm
	K
1	P1
2	P3
3	P4
4	P3
5	P1
6	P5
7	P4
8	P9
9	P1
10	P5
11	P6
12	P1
13	P3
14	P1
15	P2
16	P3
17	P1
18	P2
19	P3
20	P1
21	P1
22	P2
23	P1
24	P3
25	P1
26
27
28
29
30
31
Main

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
K1:K37	Expression	=AND(K1<>"",MOD(COUNTIF(K$1:K1,K1),5)=0)	text	NO

Hi

I have tried the code you created for me and when I just create a sheet in excel to try it it works a treat. However the sheet I need this to work with is generated with vba. I fill in a user form hit submit
and everything gets transfered to another excel sheet ( see image ). Have you any ideas how to fix this problem ?? Any help would be much appreciated.

Thankyou

 

[kanadaaa]

Doesn't it just work if you set a conditional formatting for column K on that sheet?
If it doesn't, probably the vba code has lines that eliminate such formatting and we'll have to have a look at it.