| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
CountIF by Background Color
Need a formula to count the cells in a range with a background color yellow
(6). |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
CountIF by Background Color
You can use this short UDF called CountYellow. Note that it only counts true
background color, not format generated by conditional formatting. Detecting conditional formatting is quite a bit more complicated... Public Function CountYellow(r As Range) As Double Application.Volatile = True CountColor = 0 For Each c In r If c.Interior.ColorIndex = 6 Then CountColor = CountColor + 1 End If Next End Function -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Phil H" wrote: Need a formula to count the cells in a range with a background color yellow (6). |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
CountIF by Background Color
Correction, change this line:
Application.Volatile = True to just Application.Volatile -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Luke M" wrote: You can use this short UDF called CountYellow. Note that it only counts true background color, not format generated by conditional formatting. Detecting conditional formatting is quite a bit more complicated... Public Function CountYellow(r As Range) As Double Application.Volatile = True CountColor = 0 For Each c In r If c.Interior.ColorIndex = 6 Then CountColor = CountColor + 1 End If Next End Function -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Phil H" wrote: Need a formula to count the cells in a range with a background color yellow (6). |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
CountIF by Background Color
Luke,
I put this (corrected) function code in a new module 3 in personal.xls and used the following cell formula: =COUNTYELLOW(A2:A6935), and get a #Name? error in the cell. "Luke M" wrote: Correction, change this line: Application.Volatile = True to just Application.Volatile -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Luke M" wrote: You can use this short UDF called CountYellow. Note that it only counts true background color, not format generated by conditional formatting. Detecting conditional formatting is quite a bit more complicated... Public Function CountYellow(r As Range) As Double Application.Volatile = True CountColor = 0 For Each c In r If c.Interior.ColorIndex = 6 Then CountColor = CountColor + 1 End If Next End Function -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Phil H" wrote: Need a formula to count the cells in a range with a background color yellow (6). |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
CountIF by Background Color
If your color was set by conditional format, then Luke's function will not
work. "Phil H" wrote in message ... Luke, I put this (corrected) function code in a new module 3 in personal.xls and used the following cell formula: =COUNTYELLOW(A2:A6935), and get a #Name? error in the cell. "Luke M" wrote: Correction, change this line: Application.Volatile = True to just Application.Volatile -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Luke M" wrote: You can use this short UDF called CountYellow. Note that it only counts true background color, not format generated by conditional formatting. Detecting conditional formatting is quite a bit more complicated... Public Function CountYellow(r As Range) As Double Application.Volatile = True CountColor = 0 For Each c In r If c.Interior.ColorIndex = 6 Then CountColor = CountColor + 1 End If Next End Function -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Phil H" wrote: Need a formula to count the cells in a range with a background color yellow (6). |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
CountIF by Background Color
Try:
=personal.xls!countyellow(a2:a6935) Phil H wrote: Luke, I put this (corrected) function code in a new module 3 in personal.xls and used the following cell formula: =COUNTYELLOW(A2:A6935), and get a #Name? error in the cell. "Luke M" wrote: Correction, change this line: Application.Volatile = True to just Application.Volatile -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Luke M" wrote: You can use this short UDF called CountYellow. Note that it only counts true background color, not format generated by conditional formatting. Detecting conditional formatting is quite a bit more complicated... Public Function CountYellow(r As Range) As Double Application.Volatile = True CountColor = 0 For Each c In r If c.Interior.ColorIndex = 6 Then CountColor = CountColor + 1 End If Next End Function -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Phil H" wrote: Need a formula to count the cells in a range with a background color yellow (6). -- Dave Peterson |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
CountIF by Background Color
Chip Pearson has a lot of info on counting colors at this site:
http://www.cpearson.com/excel/colors.aspx "Phil H" wrote in message ... Luke, I put this (corrected) function code in a new module 3 in personal.xls and used the following cell formula: =COUNTYELLOW(A2:A6935), and get a #Name? error in the cell. "Luke M" wrote: Correction, change this line: Application.Volatile = True to just Application.Volatile -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Luke M" wrote: You can use this short UDF called CountYellow. Note that it only counts true background color, not format generated by conditional formatting. Detecting conditional formatting is quite a bit more complicated... Public Function CountYellow(r As Range) As Double Application.Volatile = True CountColor = 0 For Each c In r If c.Interior.ColorIndex = 6 Then CountColor = CountColor + 1 End If Next End Function -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Phil H" wrote: Need a formula to count the cells in a range with a background color yellow (6). |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
CountIF by Background Color
All,
Color is not set by conditional formatting. Used =personal.xls!countyellow(a2:a6935) and got a compile error €“ variable not defined on the line CountColor =0, and #Value! Error in the cell. Tried Chips approach, with his code, and got the #Name! error. "Luke M" wrote: Correction, change this line: Application.Volatile = True to just Application.Volatile -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Luke M" wrote: You can use this short UDF called CountYellow. Note that it only counts true background color, not format generated by conditional formatting. Detecting conditional formatting is quite a bit more complicated... Public Function CountYellow(r As Range) As Double Application.Volatile = True CountColor = 0 For Each c In r If c.Interior.ColorIndex = 6 Then CountColor = CountColor + 1 End If Next End Function -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Phil H" wrote: Need a formula to count the cells in a range with a background color yellow (6). |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
CountIF by Background Color
There is an option that you're using that forces you to declare all your
variables. CountColor is not defined. Try this: Option Explicit Public Function CountYellow(r As Range) As Double Application.Volatile True Dim CountColor As Long Dim c As Range CountColor = 0 For Each c In r If c.Interior.ColorIndex = 6 Then CountColor = CountColor + 1 End If Next c 'add this line, too CountYellow = countcolor End Function As for the error you got from Chip's code... You didn't put it in a general module--or you made some other typing error in the formula. Are you sure you spelled the function name correctly (or included the "personal.xls!" characters???) You should give a little more info when things don't work. Phil H wrote: All, Color is not set by conditional formatting. Used =personal.xls!countyellow(a2:a6935) and got a compile error €“ variable not defined on the line CountColor =0, and #Value! Error in the cell. Tried Chips approach, with his code, and got the #Name! error. "Luke M" wrote: Correction, change this line: Application.Volatile = True to just Application.Volatile -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Luke M" wrote: You can use this short UDF called CountYellow. Note that it only counts true background color, not format generated by conditional formatting. Detecting conditional formatting is quite a bit more complicated... Public Function CountYellow(r As Range) As Double Application.Volatile = True CountColor = 0 For Each c In r If c.Interior.ColorIndex = 6 Then CountColor = CountColor + 1 End If Next End Function -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Phil H" wrote: Need a formula to count the cells in a range with a background color yellow (6). -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Checkbox to change background color, font color and remove/ add bo | Excel Discussion (Misc queries) | |||
| how can I conditionally change font color, or background color? | Excel Worksheet Functions | |||
| Cell background color (interior color) setting not working | Excel Programming | |||
| Default Border, Font Color, and Cell Background Color | Excel Discussion (Misc queries) | |||
| Excel 2003 Font Color and Background Color | Excel Discussion (Misc queries) | |||
![Phil H[_2_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Linear Mode
