| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to add up all cells with no fill color
Hello,
I copied a function called "CountCol" which will help me count the colored cells in my spreadsheet. I have to type the following function in my spreadsheet, =countcol(a1:a100,6), and it will count--in this case--all the cells in the range a1:a100 that have color yellow (6 stands for the color index which equals yellow). What is the color index for "no fill"? (I want to count all the cells with text which have no fill) Thanks for your help! |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to add up all cells with no fill color
Try using the Excel constant xlNone where you have the color index number in
your function -- Kevin Backmann "Studebaker" wrote: Hello, I copied a function called "CountCol" which will help me count the colored cells in my spreadsheet. I have to type the following function in my spreadsheet, =countcol(a1:a100,6), and it will count--in this case--all the cells in the range a1:a100 that have color yellow (6 stands for the color index which equals yellow). What is the color index for "no fill"? (I want to count all the cells with text which have no fill) Thanks for your help! |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to add up all cells with no fill color
Hello Kevin B,
I did and it returned a #Value! error. I wrote it like this: countcol(a1:a100,xlNone) Did I write it incorrectly? Thanks, Kris "Kevin B" wrote: Try using the Excel constant xlNone where you have the color index number in your function -- Kevin Backmann "Studebaker" wrote: Hello, I copied a function called "CountCol" which will help me count the colored cells in my spreadsheet. I have to type the following function in my spreadsheet, =countcol(a1:a100,6), and it will count--in this case--all the cells in the range a1:a100 that have color yellow (6 stands for the color index which equals yellow). What is the color index for "no fill"? (I want to count all the cells with text which have no fill) Thanks for your help! |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to add up all cells with no fill color
Try -4142, which is the value of xlNone
HTH, Bernie MS Excel MVP "Studebaker" wrote in message ... Hello, I copied a function called "CountCol" which will help me count the colored cells in my spreadsheet. I have to type the following function in my spreadsheet, =countcol(a1:a100,6), and it will count--in this case--all the cells in the range a1:a100 that have color yellow (6 stands for the color index which equals yellow). What is the color index for "no fill"? (I want to count all the cells with text which have no fill) Thanks for your help! |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to add up all cells with no fill color
No, I was dead wrong about that one. I'll see if I can find the numeric
value of the xlNone constant. -- Kevin Backmann "Studebaker" wrote: Hello Kevin B, I did and it returned a #Value! error. I wrote it like this: countcol(a1:a100,xlNone) Did I write it incorrectly? Thanks, Kris "Kevin B" wrote: Try using the Excel constant xlNone where you have the color index number in your function -- Kevin Backmann "Studebaker" wrote: Hello, I copied a function called "CountCol" which will help me count the colored cells in my spreadsheet. I have to type the following function in my spreadsheet, =countcol(a1:a100,6), and it will count--in this case--all the cells in the range a1:a100 that have color yellow (6 stands for the color index which equals yellow). What is the color index for "no fill"? (I want to count all the cells with text which have no fill) Thanks for your help! |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to add up all cells with no fill color
Try the following value, which corresponds to the Excel constant which did
not work: -4142 -- Kevin Backmann "Studebaker" wrote: Hello Kevin B, I did and it returned a #Value! error. I wrote it like this: countcol(a1:a100,xlNone) Did I write it incorrectly? Thanks, Kris "Kevin B" wrote: Try using the Excel constant xlNone where you have the color index number in your function -- Kevin Backmann "Studebaker" wrote: Hello, I copied a function called "CountCol" which will help me count the colored cells in my spreadsheet. I have to type the following function in my spreadsheet, =countcol(a1:a100,6), and it will count--in this case--all the cells in the range a1:a100 that have color yellow (6 stands for the color index which equals yellow). What is the color index for "no fill"? (I want to count all the cells with text which have no fill) Thanks for your help! |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to add up all cells with no fill color
I tried:
=countcol(a1:a100,-4142) and I got 22,000 or so for the answer. I think what it is doing is counting every no fill space rather than just the spaces that have text and no fill. Is there another color index # for "no fill" b/c I know the code I copied to create this function works? Thanks for your help. "Kevin B" wrote: Try the following value, which corresponds to the Excel constant which did not work: -4142 -- Kevin Backmann "Studebaker" wrote: Hello Kevin B, I did and it returned a #Value! error. I wrote it like this: countcol(a1:a100,xlNone) Did I write it incorrectly? Thanks, Kris "Kevin B" wrote: Try using the Excel constant xlNone where you have the color index number in your function -- Kevin Backmann "Studebaker" wrote: Hello, I copied a function called "CountCol" which will help me count the colored cells in my spreadsheet. I have to type the following function in my spreadsheet, =countcol(a1:a100,6), and it will count--in this case--all the cells in the range a1:a100 that have color yellow (6 stands for the color index which equals yellow). What is the color index for "no fill"? (I want to count all the cells with text which have no fill) Thanks for your help! |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to add up all cells with no fill color
Yes, that's the value Kevin B though migh work but I think it is just
counting every no fill cell I have in my range rather than the cells that have no fill *and* text. I appreciate your help! "Bernie Deitrick" wrote: Try -4142, which is the value of xlNone HTH, Bernie MS Excel MVP "Studebaker" wrote in message ... Hello, I copied a function called "CountCol" which will help me count the colored cells in my spreadsheet. I have to type the following function in my spreadsheet, =countcol(a1:a100,6), and it will count--in this case--all the cells in the range a1:a100 that have color yellow (6 stands for the color index which equals yellow). What is the color index for "no fill"? (I want to count all the cells with text which have no fill) Thanks for your help! |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to add up all cells with no fill color
Perhaps modify your code along these lines:
Function CountCol(r As Range, col As Variant) Dim c As Range For Each c In r If c.Interior.ColorIndex = col And _ Application.IsText(c.Value) Then _ CountCol = CountCol + 1 Next c End Function HTH, Bernie MS Excel MVP "Studebaker" wrote in message ... I tried: =countcol(a1:a100,-4142) and I got 22,000 or so for the answer. I think what it is doing is counting every no fill space rather than just the spaces that have text and no fill. Is there another color index # for "no fill" b/c I know the code I copied to create this function works? Thanks for your help. "Kevin B" wrote: Try the following value, which corresponds to the Excel constant which did not work: -4142 -- Kevin Backmann "Studebaker" wrote: Hello Kevin B, I did and it returned a #Value! error. I wrote it like this: countcol(a1:a100,xlNone) Did I write it incorrectly? Thanks, Kris "Kevin B" wrote: Try using the Excel constant xlNone where you have the color index number in your function -- Kevin Backmann "Studebaker" wrote: Hello, I copied a function called "CountCol" which will help me count the colored cells in my spreadsheet. I have to type the following function in my spreadsheet, =countcol(a1:a100,6), and it will count--in this case--all the cells in the range a1:a100 that have color yellow (6 stands for the color index which equals yellow). What is the color index for "no fill"? (I want to count all the cells with text which have no fill) Thanks for your help! |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to add up all cells with no fill color
Thank you, Bernie! I think that did the trick! I even did a rough count to
double check and think that did it!! I appreciate your help very much!!! "Bernie Deitrick" wrote: Perhaps modify your code along these lines: Function CountCol(r As Range, col As Variant) Dim c As Range For Each c In r If c.Interior.ColorIndex = col And _ Application.IsText(c.Value) Then _ CountCol = CountCol + 1 Next c End Function HTH, Bernie MS Excel MVP "Studebaker" wrote in message ... I tried: =countcol(a1:a100,-4142) and I got 22,000 or so for the answer. I think what it is doing is counting every no fill space rather than just the spaces that have text and no fill. Is there another color index # for "no fill" b/c I know the code I copied to create this function works? Thanks for your help. "Kevin B" wrote: Try the following value, which corresponds to the Excel constant which did not work: -4142 -- Kevin Backmann "Studebaker" wrote: Hello Kevin B, I did and it returned a #Value! error. I wrote it like this: countcol(a1:a100,xlNone) Did I write it incorrectly? Thanks, Kris "Kevin B" wrote: Try using the Excel constant xlNone where you have the color index number in your function -- Kevin Backmann "Studebaker" wrote: Hello, I copied a function called "CountCol" which will help me count the colored cells in my spreadsheet. I have to type the following function in my spreadsheet, =countcol(a1:a100,6), and it will count--in this case--all the cells in the range a1:a100 that have color yellow (6 stands for the color index which equals yellow). What is the color index for "no fill"? (I want to count all the cells with text which have no fill) Thanks for your help! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Add fill color in cells | Excel Discussion (Misc queries) | |||
| Fill cells with color based on criteria in two cells | Excel Worksheet Functions | |||
| HOW TO FORMATE CELLS TO COUNT CELLS WITH A FILL COLOR? | New Users to Excel | |||
| Excel 2003 will not display color fonts or color fill cells | Excel Worksheet Functions | |||
| My excel 2003 wont let me fill cells with color or color the tabs. | New Users to Excel | |||
Linear Mode
