My User-defined function doesn't update when recalculating

ctrlaltdel

Board Regular
Joined
Dec 1, 2002
Messages
60
I created the following public function to give us the color of cell shading that was used so that we could use it to get a count of how many times each shade was used:

Public Function Color(Optional rng As Range) As String
On Error GoTo NoRange
Color = rng.Item(1).Interior.ColorIndex
Exit Function
NoRange:
Color = "RANGE?"
End Function

However, when I change the color of a cell, the number doesn't automatically change. Recalculate doesn't work either. I am a super-novice here, so I sure hope one of you experts can help.

Thanks
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Re: My User-defined function doesn't update when recalculati

Try:

Code:
Public Function Color(Optional rng As Range) As String
    Application.Volatile
    On Error GoTo NoRange
    Color = rng.Item(1).Interior.ColorIndex
    Exit Function
NoRange:
    Color = "RANGE?"
End Function

The function will recalculate when the workbook is calculated. But you will have to calculate manually if you just change the colour of the Range, because changing formatting does not trigger a calculation.
 

ctrlaltdel

Board Regular
Joined
Dec 1, 2002
Messages
60
Re: My User-defined function doesn't update when recalculati

Works great!

Thanks a bunch.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,163
Messages
5,768,555
Members
425,481
Latest member
ihumanl

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top