Random select 10% for each name and Highlight it.

[BunniLove]

I am trying to generate how many cases each person is doing that day. I have that listed next to the person's name. From there I need to highlight randomly 10% of case that person does. So, for instance, if a person did 17 cases, 10% would be 2. It would go and highlight 2 random cases that person did. Can someone help? I put some screenshots.

 

[eiloken]

I am trying to generate how many cases each person is doing that day. I have that listed next to the person's name. From there I need to highlight randomly 10% of case that person does. So, for instance, if a person did 17 cases, 10% would be 2. It would go and highlight 2 random cases that person did. Can someone help? I put some screenshots.

try this:

Sub test()
    RandomHighlight Selection
End Sub

Sub RandomHighlight(ByVal rng As Range)
    Dim i As Integer, j As Integer, k As Integer
    i = WorksheetFunction.CountA(rng)
    If i = 0 Then Exit Sub
    rng.Interior.Pattern = xlNone
    For j = 1 To Int(i / 10) + 1
        k = Int(i * Rnd + 1)
        rng.Cells(k).Interior.Color = RGB(255, 255, 0)
    Next j
End Sub

 

[BunniLove]

I pasted that in VBA, but nothing happen. Can you explain what all I need to change or do?

 

[BunniLove]

I think I need to clarified. I have a list of employee and next to their name is the number of time the name display, look at the screenshot LIST. From there I want to highlight 10% of each employee cases but randomly. So for instance, Amanda Pegago did 17 so 10% will be 2. I need to highlight her name on the screenshot case randomly 2 of her name there.

 

[eiloken]

I think I need to clarified. I have a list of employee and next to their name is the number of time the name display, look at the screenshot LIST. From there I want to highlight 10% of each employee cases but randomly. So for instance, Amanda Pegago did 17 so 10% will be 2. I need to highlight her name on the screenshot case randomly 2 of her name there.

you need cleared it first, with this code you need select to cell that has name of person you need to highlight then run sub "Highlight10pctCell". in your examples, you need select the cell that has value is "Amanda Pegago" in "Case" then run "Highlight10pctCell":

Sub Highlight10pctCell()
    Dim DataRange As Range
    Dim WhoToFind As Range
    Set WhoToFind = Selection.Cells(1) 'set person you need to highlight to first cell of selection range, you need to select persion that you want to highlight and run this sub
    Set DataRange = Range(Cells(1, WhoToFind.Column), Cells(Rows.Count, WhoToFind.Column).End(xlUp)) 'set highlight range to columns that has cell you selected, you can also change to whatever range you want
    RandomHighlight criteria:=WhoToFind, rng:=DataRange
End Sub

Private Sub RandomHighlight(ByVal criteria As Variant, ByVal rng As Range)
    Dim i As Integer, j As Integer, k As Integer, t As Integer
    Dim cll As Range
    Dim icoll As New Collection
    i = WorksheetFunction.CountIf(rng, criteria)
    If i = 0 Then Exit Sub
    rng.Interior.Pattern = xlNone
    For Each cll In rng
        If Not IsEmpty(cll) And Not IsError(cll) Then
            If UCase(Trim(cll.Value)) = UCase(Trim(criteria)) Then
                icoll.Add cll
                t = t + 1
                If t = i Then Exit For
            End If
        End If
    Next cll
    For j = 1 To Int(i / 10) + 1
        k = Int(i * Rnd + 1)
        icoll(k).Interior.Color = RGB(255, 255, 0)
    Next j
End Sub

 

[BunniLove]

This work only for anything that is above 10 cases. So, if a person does least then 10, I still want to highlight at least 1 case. In the code I try to change it....this is what I put. But it will say can't execute code in break mode.
For Each cll In rng
If Not IsEmpty(cll) And Not IsError(cll) Then
If UCase(Trim(cll.Value)) = UCase(Trim(criteria)) Then
icoll.Add cll
t = t + 1
If t = i Then
icoll(k).Interior.Color = RGB(255, 255, 0) Then Exit For
End If
End If
Next cll
For j = 1 To Int(i / 10) + 1
k = Int(i * Rnd + 1)
icoll(k).Interior.Color = RGB(255, 255, 0)
Next j
End Sub

 

[eiloken]

This work only for anything that is above 10 cases. So, if a person does least then 10, I still want to highlight at least 1 case. In the code I try to change it....this is what I put. But it will say can't execute code in break mode.
For Each cll In rng
If Not IsEmpty(cll) And Not IsError(cll) Then
If UCase(Trim(cll.Value)) = UCase(Trim(criteria)) Then
icoll.Add cll
t = t + 1
If t = i Then
icoll(k).Interior.Color = RGB(255, 255, 0) Then Exit For
End If
End If
Next cll
For j = 1 To Int(i / 10) + 1
k = Int(i * Rnd + 1)
icoll(k).Interior.Color = RGB(255, 255, 0)
Next j
End Sub

I work for me, that int(i/10)+1 always greater or equal with 1, so when you select 1 cell, it will has at leats 1 highlight cell

 

[eiloken]

I work for me, that int(i/10)+1 always greater or equal with 1, so when you select 1 cell, it will has at leats 1 highlight cell

 

[BunniLove]

I believe it's because I need to clear it every time I run it. So, I should just put j.ClearContent before Next J?

 

[eiloken]

I believe it's because I need to clear it every time I run it. So, I should just put j.ClearContent before Next J?

No, with my code, you just need select a cell in your case picture that contains a name then run highlight10pctcell then it will auto detect all same name cells and highlight it