CountColourIF

L

LexB

New Member
Joined
Aug 4, 2004
Messages
5
I have a module in VB which counts the number of cells of a specified colour in a specified range, in a spreadsheet. See below:-

Function CountColor(Rng As Range, RngColor As Range) As Integer

Dim sell As Range
Dim Clr As Long
Clr = RngColor.Range("A1").Interior.Color
For Each sell In Rng
If sell.Interior.Color = Clr Then
CountColor = CountColor + 1
End If
Next sell

End Function

I now want another code / formula which will be more of a 'Countcolourif' style formula. I want it to count coloured cells if they contain a value = to zero, and in another case if they are >0.

I have no idea where to begin!! Any help / advice would be welcomed!

LexB
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
Sort by date Sort by votes
Simply expand the IF statement part of the function, i.e. if you want to count if the it meets the color criteria and is equal to zero, it would look like:

If (sell.Interior.Color = Clr) And (sell.Value=0) Then
 
Upvote 0
Thanks for that it worked perfectly!! This is going to sound daft now: How do I rename the function so that it doesn't interfere with the CountColour formula which I already have in place elsewhere on the speadsheet?

Below is the now amended code: -

Function CountColor(Rng As Range, RngColor As Range) As Integer

Dim sell As Range
Dim Clr As Long
Clr = RngColor.Range("A1").Interior.Color
For Each sell In Rng
If (sell.Interior.Color = Clr) And (sell.Value = 0) Then
CountColor = CountColor + 1
End If
Next sell

End Function

Thanks!!

Lex
 
Upvote 0
Simply Change it to any valid aplhanumeric name in the title. For example, if you wanted to rename it LexColor, simply change the first line to:

Function LexColor(Rng As Range, RngColor As Range) As Integer
 
Upvote 0
But do I have to change it at any other point in the code? Did try changing just the name but it errored!
 
Upvote 0
You need to change wherever you are calling it from. User Defined Functions can be called from other VBA code, or directly from the spreadsheet. So, wherever you are calling this function, make sure you change the name where you are calling it.
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question
Count coloured cells in a range which has been conditionally formatted
Replies
1
Views
331
awoohaw
awoohaw
S
  • Solved
How to make GetColorCount (custom function) work with Conditional Formatting?
Replies
5
Views
498
Fluff
Fluff
P
  • Locked
  • Question
Conditional Format gradient low number to high number
Replies
1
Views
397
Fluff
Fluff
M
  • Question
VBA help
Replies
1
Views
242
Joe4
Joe4
GMC The Macro Man
  • Solved
Calculations are not updating
Replies
4
Views
579
GMC The Macro Man
GMC The Macro Man

Forum statistics

Threads
1,215,029
Messages
6,122,760
Members
449,095
Latest member
m_smith_solihull

Share this page

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