Find and Highlight Least Common Occurrence(s) in Variable Range

Question

I have a code that has a variable range with many categories in each column that display data. I need to highlight the least commonly occurring values as a percentage of the total number of cells.

If there are 300 cells in the column, it needs to find the value (out of many possibly repeating values) that occurs least frequently. It is a bonus if the code can anticipate the total number, and give only 5% or 10% of the entire column as a result.

Currently my attempt is to use a function in the top cell that will find the least common occurrence, and the code will simply highlight whatever that value is down the cell as it repeats (and highlight every one of the least common ones.

The difficulty I am having is twofold.

1. There may be more than one least common value that is still below 10% of the total values
2. The ability to automate this search so that it may be performed and highlighted for all of more than 100 columns with different categories and different values in each column

If too vague, feel free to ask questions about what I am going for, and I will respond promptly.

This is what the data looks like. As you can see there are merged titles for each column with various blank spaces and sperratically placed data that matches some specific column.

This is the proposed code which is still not highlighting what I would like it to. It has two problems. 1: It will highlight ALL of the data in one range if there is no differing value in the row. 2: It will highlight the titles of the columns.

This is the highlighted data which is still insufficiently complete.

In some cases the column truely do not match the purpose of the code, for example in one column, the number 12 was highlighted down the column (67 occurances) where there are fewer occurances of other numbers. (8 occurs 29 times and is not highlighted)

Answer 1

I just hacked together a seemingly working example. Try this here:

Sub frequenz()
Dim col As Range, cel As Range
Dim letter As String
Dim lookFor As String
Dim frequency As Long, totalRows As Long
Dim relFrequency As Double
Dim ran As Range

ran = ActiveSheet.Range("A1:ZZ65535")
totalRows = 65535

For Each col In ran.Columns
    '***get column letter***
    letter = Split(ActiveSheet.Cells(1, col.Column).Address, "$")(1)
    '*******
    For Each cel In col.Cells
        lookFor = cel.Text
        frequency = Application.WorksheetFunction.CountIf(Range(letter & "2:" & letter & totalRows), lookFor)
        relFrequency = frequency / totalRows

        If relFrequency <= 0.001 Then
            cel.Interior.Color = ColorConstants.vbYellow
        End If
    Next cel

Next col

End Sub

It seemed to be doing just what you are looking for.

Edit: fixed the address getting.