• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Use UDF within Index and Match

Mofi454

New Member
I have a UDF that performs a count function based on background colour. I wish to use the UDF within an Index and Match function.
What I would like to do is for the Index function to use my UDF to sum the different colours within my sheet.
So instead of it performing say
Index(b7:T30,(match(V7,A7:A30,0),Match(W5,B5:T5,0)) and returning the value of the cell
I would like it to perform the following
Index(GetColourCount(b7:T30,V6),(match(V7,A7:A30,0),Match(W5,B5:T5,0)) and return the count of the colours

Thanks
 

Attachments

  • Book1.test.xlsm
    17.6 KB · Views: 2
Why not change the UDF to
Code:
Function GetColourCount(CountRange As Range, CountColor As Range, Dept As String, Yr As String) As Long

    Dim CountColourValue As Integer
    Dim TotalCount As Integer
    
    CountColourValue = CountColor.Interior.ColorIndex
    Set rcell = CountRange
    
    For Each rcell In CountRange
      If Cells(rcell.Row, 1) = Dept And Cells(5, rcell.Column) = Yr Then
         If rcell.Interior.ColorIndex = CountColourValue Then
           TotalCount = TotalCount + 1
         End If
      End If
    Next rcell
    GetColourCount = TotalCount

End Function
Used like
=GetColourCount($B$7:$T$30,$V$6,$V7,W$5)
 
Thank you for your help. That works perfectly.
Is there a way for me to get the UDF to recalculate everytime I change the background colour within the range B7:T30 without using Application Volatile?

Thanks
 
You could use a Worksheet_SelectionChange event to recalculate the sheet, but I think that's about it.
Excel does not recognise a cell colour being changed.
 
So I tried the following, but it doesn't work

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Target.Worksheet.Range("$B$7:$T$30")) Is Nothing Then
        Application.EnableEvents = False
            Application.CalculateFull
        Application.EnableEvents = True
    End If
End Sub
 
Last edited by a moderator:
It won't work on a change event, it needs to be a SelectionChange like
Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

   If Not Intersect(Target, Range("B7:T30")) Is Nothing Then
      Me.Calculate
   End If
End Sub
 
Back
Top