SOLVED

Excel Showing #blocked! after sorting

%3CLINGO-SUB%20id%3D%22%5C%26quot%3Blingo-sub-3141047%5C%26quot%3B%22%20slang%3D%22%5C%26quot%3Ben-US%5C%26quot%3B%22%3EExcel%20Showing%20%23blocked!%20after%20sorting%26lt%3B%5C%2Flingo-sub%26gt%3B%3CLINGO-BODY%20id%3D%22%5C%26quot%3Blingo-body-3141047%5C%26quot%3B%22%20slang%3D%22%5C%26quot%3Ben-US%5C%26quot%3B%22%3E%3CP%3EA%20good%20day%20to%20everyone.%20I%20am%20facing%20an%20issue%20with%20this%20function%20i%20have%20defined%20in%20order%20to%20count%20a%20specific%20line%20of%20items.%20I%20have%20used%20the%20GET.CELL%20to%20extract%20the%20colour%20ID.%20The%20reference%20cell%20is%20H2%20onwards.%20I%26nbsp%3B%20could%20successfully%20count%20the%20number%20of%20colours%20according%20to%20the%20ID%3CBR%20%2F%3E%3CBR%20%2F%3EHowever%2C%20when%20I%20tried%20sorting%20out%20Column%20I%20to%20see%20how%20many%20were%20in%20Red%20font.%20The%20colour%20codes%20went%20haywire%20and%20started%20showing%20%23BLOCKED!%20even%20after%20undoing%20the%20sort%20it%20still%20stays%20as%20that%20error.%20I%20tried%20redefining%20the%20function%20again%20and%20it%20does%20not%20work%20anymore.%20Seem%20to%20be%20at%20my%20wits%20end%20here%20and%20I%20need%20to%20rush%20out%20the%20report.%26nbsp%3B%3CBR%20%2F%3E%3CBR%20%2F%3ECould%20anyone%20help%3F%26nbsp%3B%20Would%20greatly%20appreciate%20it.%26nbsp%3B%26lt%3B%5C%2FP%26gt%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%26lt%3B%5C%2FP%26gt%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22%5C%26quot%3Blia-inline-image-display-wrapper%22%20lia-image-align-inline%3D%22%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Fgxcuf89792%2F%5C%26quot%3Bhttps%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F346507i316FA0D8E2340964%2Fimage-dimensions%2F601x209%3Fv%3Dv2%5C%26quot%3B%22%20width%3D%22%5C%26quot%3B601%5C%26quot%3B%22%20height%3D%22%5C%26quot%3B209%5C%26quot%3B%22%20role%3D%22%5C%26quot%3Bbutton%5C%26quot%3B%22%20title%3D%22Michaelkoh89_2-1644384496221.png%22%20alt%3D%22%5C%26quot%3BMichaelkoh89_2-1644384496221.png%5C%26quot%3B%22%20%2F%3E%26lt%3B%5C%2Fspan%26gt%3B%26lt%3B%5C%2FP%26gt%3B%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%26lt%3B%5C%2FP%26gt%3B%3C%2FP%3E%3CP%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CSPAN%20class%3D%22%5C%26quot%3Blia-inline-image-display-wrapper%22%20lia-image-align-inline%3D%22%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Fgxcuf89792%2F%5C%26quot%3Bhttps%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F346504i2A7BEA0B122E5394%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%5C%26quot%3B%22%20role%3D%22%5C%26quot%3Bbutton%5C%26quot%3B%22%20title%3D%22Excel%20issue%202.PNG%22%20issue%3D%22%22%202.png%3D%22%22%20alt%3D%22Excel%20issue%202.PNG%22%20%2F%3E%26lt%3B%5C%2Fspan%26gt%3B%3CSPAN%20class%3D%22%5C%26quot%3Blia-inline-image-display-wrapper%22%20lia-image-align-inline%3D%22%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Fgxcuf89792%2F%5C%26quot%3Bhttps%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F346503iE8530D5A87685C5E%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%5C%26quot%3B%22%20role%3D%22%5C%26quot%3Bbutton%5C%26quot%3B%22%20title%3D%22Excel%20issue%201.PNG%22%20issue%3D%22%22%201.png%3D%22%22%20alt%3D%22Excel%20issue%201.PNG%22%20%2F%3E%26lt%3B%5C%2Fspan%26gt%3B%26lt%3B%5C%2FP%26gt%3B%26lt%3B%5C%2Flingo-body%26gt%3B%3CLINGO-LABS%20id%3D%22%5C%26quot%3Blingo-labs-3141047%5C%26quot%3B%22%20slang%3D%22%5C%26quot%3Ben-US%5C%26quot%3B%22%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analysis%26lt%3B%5C%2Flingo-label%26gt%3B%3CLINGO-LABEL%3EExcel%26lt%3B%5C%2Flingo-label%26gt%3B%3CLINGO-LABEL%3EExcel%20for%20web%26lt%3B%5C%2Flingo-label%26gt%3B%3CLINGO-LABEL%3EExcel%20on%20mobile%26lt%3B%5C%2Flingo-label%26gt%3B%3CLINGO-LABEL%3EFormulas%20and%20Functions%26lt%3B%5C%2Flingo-label%26gt%3B%3CLINGO-LABEL%3EMacros%20and%20VBA%26lt%3B%5C%2Flingo-label%26gt%3B%3CLINGO-LABEL%3EOffice%20365%26lt%3B%5C%2Flingo-label%26gt%3B%26lt%3B%5C%2Flingo-labs%26gt%3B%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3C%2FLINGO-SUB%3E
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. 

 

Michaelkoh89_2-1644384496221.png

 




Excel issue 2.PNGExcel issue 1.PNG

2 Replies
best response confirmed by Sergei Baklan (MVP)
Solution

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...

 

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
          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

 

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