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

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

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.
 

Forum statistics

Threads
1,186,164
Messages
5,956,302
Members
438,246
Latest member
Deelea

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