Highlight a cell only when a value has appeared 5 times

tjp500

New Member
Joined
Feb 14, 2021
Messages
4
Office Version
  1. 2016
Platform
  1. Windows
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 :)
 

Attachments

  • Annotation 2021-02-14 151649.png
    Annotation 2021-02-14 151649.png
    8 KB · Views: 7

Some videos you may like

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,253
Office Version
  1. 365
Platform
  1. Windows
Hi & welcome to MrExcel.
How about
+Fluff 1.xlsm
K
1P1
2P3
3P4
4P3
5P1
6P5
7P4
8P9
9P1
10P5
11P6
12P1
13P3
14P1
15P2
16P3
17P1
18P2
19P3
20P1
21P1
22P2
23P1
24P3
25P1
26
27
28
29
30
31
Main
Cells with Conditional Formatting
CellConditionCell FormatStop If True
K1:K37Expression=AND(K1<>"",MOD(COUNTIF(K$1:K1,K1),5)=0)textNO
 

tjp500

New Member
Joined
Feb 14, 2021
Messages
4
Office Version
  1. 2016
Platform
  1. Windows
Hi & welcome to MrExcel.
How about
+Fluff 1.xlsm
K
1P1
2P3
3P4
4P3
5P1
6P5
7P4
8P9
9P1
10P5
11P6
12P1
13P3
14P1
15P2
16P3
17P1
18P2
19P3
20P1
21P1
22P2
23P1
24P3
25P1
26
27
28
29
30
31
Main
Cells with Conditional Formatting
CellConditionCell FormatStop If True
K1:K37Expression=AND(K1<>"",MOD(COUNTIF(K$1:K1,K1),5)=0)textNO
Thank you very much that worked a treat :):)
 

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
4,570
Office Version
  1. 365
Platform
  1. MacOS
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,253
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Glad we could help & thanks for the feedback.
 

kanadaaa

Active Member
Joined
Dec 29, 2019
Messages
291
Office Version
  1. 365
Platform
  1. Windows
You could also do it with VBA (works only if the first data is in row 2)
VBA Code:
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

New Member
Joined
Feb 14, 2021
Messages
4
Office Version
  1. 2016
Platform
  1. Windows
Hi & welcome to MrExcel.
How about
+Fluff 1.xlsm
K
1P1
2P3
3P4
4P3
5P1
6P5
7P4
8P9
9P1
10P5
11P6
12P1
13P3
14P1
15P2
16P3
17P1
18P2
19P3
20P1
21P1
22P2
23P1
24P3
25P1
26
27
28
29
30
31
Main
Cells with Conditional Formatting
CellConditionCell FormatStop If True
K1:K37Expression=AND(K1<>"",MOD(COUNTIF(K$1:K1,K1),5)=0)textNO
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
 

Attachments

  • pic1.png
    pic1.png
    54.2 KB · Views: 4

kanadaaa

Active Member
Joined
Dec 29, 2019
Messages
291
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,126,994
Messages
5,622,053
Members
415,875
Latest member
Tarali

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top