Excel Showing #blocked! after sorting

Question

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.

jmb17 · Accepted Answer

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.https://techcommunity.microsoft.com/t5/excel-blog/excel-4-0-xlm-macros-now-restricted-by-default-for-customer/ba-p/3057905&nbsp;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.&nbsp;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
          Else
               Err.Raise Number:=vbObjectError + 513
          End If
     Else
          Set cell = rng.Cells(1)
     End If
     
     GetColorIndex = cell.Interior.ColorIndex

ExitProc:
     Exit Function
     
ErrHandler:
     GetColorIndex = CVErr(xlErrValue)
     
End Function&nbsp;

Forum Discussion

Excel Showing #blocked! after sorting

2 Replies

Resources