Feb 08 2022 09:30 PM
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.
Feb 08 2022 10:20 PM - edited Feb 08 2022 10:37 PM
SolutionIt 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
Feb 15 2022 09:57 PM
Feb 08 2022 10:20 PM - edited Feb 08 2022 10:37 PM
SolutionIt 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