(VBA) Refresh cell if another cell in a range changes colour

KVexcel

New Member
Joined
Sep 13, 2022
Messages
14
Office Version
  1. 2021
Platform
  1. Windows
So I have this function that counts cells of a color on a range
VBA Code:
Function GetColorCount(CountRange As Range, CountColor As Range)
Dim CountColorValue As Integer
Dim TotalCount As Integer
CountColorValue = CountColor.Interior.ColorIndex
Set rCell = CountRange
For Each rCell In CountRange
  If rCell.Interior.ColorIndex = CountColorValue Then
    TotalCount = TotalCount + 1
  End If
Next rCell
GetColorCount = TotalCount
End Function
I want the function cell to be refreshed each time a cell on the given range changes colour. Also, is there a way to incorporate this inside the function code or does it have to be a seperate sub ?
 
Are all UDFs volatile by default when they are created ?
I looked it up here Application.Volatile method (Excel)
I added Application.Volatile to GetColorCount and it still doesn't work. Could it be that cells on the A2:B5001 range originate from the spill from the SORT in cell B2?
Or is selecting a cell irrelevant on it belonging to an array?
 
Upvote 0

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
Are all UDFs volatile by default when they are created ?
No they are not, they will normally only calculate when needed.

Did you put the selectionchange event into the "Easy Tags" sheets code module?
 
Upvote 0
The modules are all like this, no module is on a specific sheet as far as I can undetstand.

n.jpg

Also, I'd like to thank you for all your help so far. I hope one day I learn enough about excel and VBA so I can in turn help answer other people's questions 😊
 
Upvote 0
The change event MUST go in the relevant sheet module.
 
Upvote 0
The folder Modules was auto-created when I created the first module. Should I rightclick and uncheck toggle folders like this?

n.jpg

Is there an option to put modules and sheets on the same folder ?
 
Upvote 0
No, you just need to copy the selectionchange event & paste it in the "Easy Tags" code module
 
Upvote 0
Glad to help & thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,223,099
Messages
6,170,107
Members
452,302
Latest member
TaMere

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