Function to get cell format

[gijimbo]

I want to write a function that compares the format of the cell it's used in to the formats of each cell in a dynamic list of cells.

Once I've got the format (style and font color) stored in an array, I will step through the list of cells and compare their style and font colors to those stored in the array. If both match a counter variable is incremented.

I have a good idea how I'm going to do the comparison and I know how to get the formating of the "list cells".

Here's my big problem:
I'm not sure how to get the format of whatever cell uses the function.

 

[GlennUK]

Why do you want to do this? What's the final aim of what you are going to do?

 

[gijimbo]

I want to display the number of cells in the list that match the format of the cell the function is used in. Sorry, I forgot to mention what I was trying to do.

 

[gijimbo]

Here's what I've got so far.
Note: I'm just using "Selection" as a place holder until I know how to get the specific cell calling the function.

Function User(name As String)

Dim mainL As Integer, _
    i As Integer, _
    j As Integer, _
    fmt(2) As Variant, _
    tStyle As Variant, _
    tFColor As Variant
        
mainL = Sheets("Sheet1").Range("B2").End(xlDown).Row

fmt(0) = 0
fmt(1) = Selection.Style
fmt(2) = Selection.Font.Color

For i = 2 To mainL
    With Cells(i, 2)
        tStyle = .Style
        tFColor = .Font.Color
    End With
    If fmt(1) = tStyle And fmt(2) = tFColor Then
        fmt(0) = fmt(0) + 1
    End If
Next i

User = name & " (" & fmt(0) & ")"

End Function

 

[GlennUK]

What is "name" that you pass to the function? You could pass a cell ref to the function, and pass it the cell ref of the cell that it's in.

 

[gijimbo]

The list is a list of tasks that need to be completed.
There are a number of users on a team that complete those tasks.
Each user has a particular format assigned to them and their tasks are highlighted their color.

"name" is the user's name.

The function is essentially a way for every one to easily see how many tasks they are in charge of.

You could pass a cell ref to the function, and pass it the cell ref of the cell that it's in.

So in the cell you're saying type something like this? =User("Bob", J1)

I guess that could work for now, thanks. I just figured there had to be a way to get the location of the cell a function is called from. (still hoping, crosses fingers)

 

[GlennUK]

Application.Caller will return the cell that called the function.

 

[gijimbo]

Got it, thanks!