Excel Showing #blocked! after sorting

Occasional Contributor

A good day to everyone. I am facing an issue with this function i have defined in order to count a specific line of items. I have used the GET.CELL to extract the colour ID. The reference cell is H2 onwards. I  could successfully count the number of colours according to the ID

However, when I tried sorting out Column I to see how many were in Red font. The colour codes went haywire and started showing #BLOCKED! even after undoing the sort it still stays as that error. I tried redefining the function again and it does not work anymore. Seem to be at my wits end here and I need to rush out the report. 

Could anyone help?  Would greatly appreciate it. 




Excel issue 2.PNGExcel issue 1.PNG

2 Replies
best response confirmed by Sergei Baklan (MVP)

It appears there was a recent update to disable xlm macro functions. But, it also appears there is a trust center setting for it, unless your organization has restricted it.


Alternatively, you might try a custom function, copy the code into a standard module in the vba editor. Then, use it like an excel function =GetColorIndex(A1) or just GetColorIndex() to return the value of the cell in which the function is entered.


Public Function GetColorIndex(Optional rng As Range) As Variant
     Dim cell As Range
     On Error GoTo ErrHandler
     Application.Volatile True
     If rng Is Nothing Then
          If TypeName(Application.Caller) = "Range" Then
               Set cell = Application.Caller
               Err.Raise Number:=vbObjectError + 513
          End If
          Set cell = rng.Cells(1)
     End If
     GetColorIndex = cell.Interior.ColorIndex
     Exit Function
     GetColorIndex = CVErr(xlErrValue)
End Function


Thanks very much. I managed to open the VBA editor and put the code in. Now it shows correctly.