Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to use a formula to determine the Cell Style?
I'm trying to use a formula to determine the Cell (custom) Style? I believe
that once I have this formula figured out I will be able to use it to "Count" the number of cells with a particular Style and display this. Any ideas? Thanks, Gerard |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to use a formula to determine the Cell Style?
By "style" do you mean color? If so, Chip Pearson has some great
write-ups on dealing with colors. http://www.cpearson.com/excel/colors.htm Gerard wrote: I'm trying to use a formula to determine the Cell (custom) Style? I believe that once I have this formula figured out I will be able to use it to "Count" the number of cells with a particular Style and display this. Any ideas? Thanks, Gerard |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to use a formula to determine the Cell Style?
Well - in this particular case "color" might work. I will check the link you
mentioned. What I was really looking for was for a formula that when pointed at a cell would actually tell me the excel "Style" that was being used. They added several custom styles - that currently only change the cell color. I was hoping to react to cells or get counts of cells that had a particular style applied to them. AND that you for the info! Gerard "JW" wrote: By "style" do you mean color? If so, Chip Pearson has some great write-ups on dealing with colors. http://www.cpearson.com/excel/colors.htm Gerard wrote: I'm trying to use a formula to determine the Cell (custom) Style? I believe that once I have this formula figured out I will be able to use it to "Count" the number of cells with a particular Style and display this. Any ideas? Thanks, Gerard |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to use a formula to determine the Cell Style?
I wrote a simple function to return the style of a given cell.
Function Cellstyle(inp As Range) As String Cellstyle = inp.Style End Function Assume you have some data in cell A1. In B1 enter =Cellstyle(A1). HTH, JP On Nov 13, 4:13 pm, Gerard wrote: I'm trying to use a formula to determine the Cell (custom) Style? I believe that once I have this formula figured out I will be able to use it to "Count" the number of cells with a particular Style and display this. Any ideas? Thanks, Gerard |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to use a formula to determine the Cell Style?
JP,
Awesome! I've written maybe a zillion Excel 4 Macros doing various things from Payroll to AP input etc. I never really got into VB. This was simple, easy and worked perfectly. I appreceiate the help! Peace, Gerard "JP" wrote: I wrote a simple function to return the style of a given cell. Function Cellstyle(inp As Range) As String Cellstyle = inp.Style End Function Assume you have some data in cell A1. In B1 enter =Cellstyle(A1). HTH, JP On Nov 13, 4:13 pm, Gerard wrote: I'm trying to use a formula to determine the Cell (custom) Style? I believe that once I have this formula figured out I will be able to use it to "Count" the number of cells with a particular Style and display this. Any ideas? Thanks, Gerard |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to use a formula to determine the Cell Style?
JP,
I hate to abuse this site, but I think you would be the best one to ask since you created the function. I'm trying to get a count of various cells that contain particular styles. I tried this: "=COUNTIF(B4:P53,Cellstyle(Accent1))". I believe this is closer to correct (and I tried it as well): =COUNTIF(B4:P53,Cellstyle("Accent1")). They are returning 0 as the count. I'm not sure why though. Any ideas? Thanks again, Gerard "JP" wrote: I wrote a simple function to return the style of a given cell. Function Cellstyle(inp As Range) As String Cellstyle = inp.Style End Function Assume you have some data in cell A1. In B1 enter =Cellstyle(A1). HTH, JP On Nov 13, 4:13 pm, Gerard wrote: I'm trying to use a formula to determine the Cell (custom) Style? I believe that once I have this formula figured out I will be able to use it to "Count" the number of cells with a particular Style and display this. Any ideas? Thanks, Gerard |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to use a formula to determine the Cell Style?
Glad to help!
--JP On Nov 14, 9:40 am, Gerard wrote: JP, Awesome! I've written maybe a zillion Excel 4 Macros doing various things from Payroll to AP input etc. I never really got into VB. This was simple, easy and worked perfectly. I appreceiate the help! Peace, Gerard "JP" wrote: I wrote a simple function to return the style of a given cell. Function Cellstyle(inp As Range) As String Cellstyle = inp.Style End Function Assume you have some data in cell A1. In B1 enter =Cellstyle(A1). HTH, JP On Nov 13, 4:13 pm, Gerard wrote: I'm trying to use a formula to determine the Cell (custom) Style? I believe that once I have this formula figured out I will be able to use it to "Count" the number of cells with a particular Style and display this. Any ideas? Thanks, Gerard- Hide quoted text - - Show quoted text - |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to use a formula to determine the Cell Style?
Function Cellstylecount(inp As Range, rRange As Range) As Long
Dim cell As Range count = 0 For Each cell In inp If cell.Style = rRange.Style Then count = count + 1 End If Next cell Cellstylecount = count End Function Assuming "Accent1" is a named cell containing the style you want to check against. Instead of your COUNTIF formula, try =Cellstylecount(B4:P53,Accent1) HTH, JP On Nov 14, 9:41 am, Gerard wrote: JP, I hate to abuse this site, but I think you would be the best one to ask since you created the function. I'm trying to get a count of various cells that contain particular styles. I tried this: "=COUNTIF(B4:P53,Cellstyle(Accent1))". I believe this is closer to correct (and I tried it as well): =COUNTIF(B4:P53,Cellstyle("Accent1")). They are returning 0 as the count. I'm not sure why though. Any ideas? Thanks again, Gerard "JP" wrote: I wrote a simple function to return the style of a given cell. Function Cellstyle(inp As Range) As String Cellstyle = inp.Style End Function Assume you have some data in cell A1. In B1 enter =Cellstyle(A1). HTH, JP On Nov 13, 4:13 pm, Gerard wrote: I'm trying to use a formula to determine the Cell (custom) Style? I believe that once I have this formula figured out I will be able to use it to "Count" the number of cells with a particular Style and display this. Any ideas? Thanks, Gerard- Hide quoted text - - Show quoted text - |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to use a formula to determine the Cell Style?
Accent1, Accent2, Accent3.... Etc... Are the names of the "Styles" that I
am trying to count. I tried it quickly and it returned #Value, but I will now look at it more and try to understand it, then perhaps I will have more luck. Thank you for the input. Peace, Gerard "JP" wrote: Function Cellstylecount(inp As Range, rRange As Range) As Long Dim cell As Range count = 0 For Each cell In inp If cell.Style = rRange.Style Then count = count + 1 End If Next cell Cellstylecount = count End Function Assuming "Accent1" is a named cell containing the style you want to check against. Instead of your COUNTIF formula, try =Cellstylecount(B4:P53,Accent1) HTH, JP On Nov 14, 9:41 am, Gerard wrote: JP, I hate to abuse this site, but I think you would be the best one to ask since you created the function. I'm trying to get a count of various cells that contain particular styles. I tried this: "=COUNTIF(B4:P53,Cellstyle(Accent1))". I believe this is closer to correct (and I tried it as well): =COUNTIF(B4:P53,Cellstyle("Accent1")). They are returning 0 as the count. I'm not sure why though. Any ideas? Thanks again, Gerard "JP" wrote: I wrote a simple function to return the style of a given cell. Function Cellstyle(inp As Range) As String Cellstyle = inp.Style End Function Assume you have some data in cell A1. In B1 enter =Cellstyle(A1). HTH, JP On Nov 13, 4:13 pm, Gerard wrote: I'm trying to use a formula to determine the Cell (custom) Style? I believe that once I have this formula figured out I will be able to use it to "Count" the number of cells with a particular Style and display this. Any ideas? Thanks, Gerard- Hide quoted text - - Show quoted text - |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to use a formula to determine the Cell Style?
Try this:
Function Cellstylecheck(inp As Range, sStyle As String) As Long Dim cell As Range Count = 0 For Each cell In inp If cell.Style = sStyle Then Count = Count + 1 End If Next cell Cellstylecheck = Count End Function =Cellstylecheck(B4:P53,"Accent1") Or even better, create a dropdown/validation list in a cell (say, A1) then enter =Cellstylecheck(B4:P53,A1). HTH, JP On Nov 14, 10:44 am, Gerard wrote: Accent1, Accent2, Accent3.... Etc... Are the names of the "Styles" that I am trying to count. I tried it quickly and it returned #Value, but I will now look at it more and try to understand it, then perhaps I will have more luck. Thank you for the input. Peace, Gerard "JP" wrote: Function Cellstylecount(inp As Range, rRange As Range) As Long Dim cell As Range count = 0 For Each cell In inp If cell.Style = rRange.Style Then count = count + 1 End If Next cell Cellstylecount = count End Function Assuming "Accent1" is a named cell containing the style you want to check against. Instead of your COUNTIF formula, try =Cellstylecount(B4:P53,Accent1) HTH, JP On Nov 14, 9:41 am, Gerard wrote: JP, I hate to abuse this site, but I think you would be the best one to ask since you created the function. I'm trying to get a count of various cells that contain particular styles. I tried this: "=COUNTIF(B4:P53,Cellstyle(Accent1))". I believe this is closer to correct (and I tried it as well): =COUNTIF(B4:P53,Cellstyle("Accent1")). They are returning 0 as the count. I'm not sure why though. Any ideas? Thanks again, Gerard "JP" wrote: I wrote a simple function to return the style of a given cell. Function Cellstyle(inp As Range) As String Cellstyle = inp.Style End Function Assume you have some data in cell A1. In B1 enter =Cellstyle(A1). HTH, JP On Nov 13, 4:13 pm, Gerard wrote: I'm trying to use a formula to determine the Cell (custom) Style? I believe that once I have this formula figured out I will be able to use it to "Count" the number of cells with a particular Style and display this. Any ideas? Thanks, Gerard- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to use a formula to determine the Cell Style?
JP,
I vaguely understand this (the function). Indeed, it does work fine. I was easily able to modify to get all the "Accent#" counts in different cells. I owe you a beer - or a coffee - or whatever you would like to wet your whistle. Thank you very much for all the help. Much peace, Gerard "JP" wrote: Try this: Function Cellstylecheck(inp As Range, sStyle As String) As Long Dim cell As Range Count = 0 For Each cell In inp If cell.Style = sStyle Then Count = Count + 1 End If Next cell Cellstylecheck = Count End Function =Cellstylecheck(B4:P53,"Accent1") Or even better, create a dropdown/validation list in a cell (say, A1) then enter =Cellstylecheck(B4:P53,A1). HTH, JP On Nov 14, 10:44 am, Gerard wrote: Accent1, Accent2, Accent3.... Etc... Are the names of the "Styles" that I am trying to count. I tried it quickly and it returned #Value, but I will now look at it more and try to understand it, then perhaps I will have more luck. Thank you for the input. Peace, Gerard "JP" wrote: Function Cellstylecount(inp As Range, rRange As Range) As Long Dim cell As Range count = 0 For Each cell In inp If cell.Style = rRange.Style Then count = count + 1 End If Next cell Cellstylecount = count End Function Assuming "Accent1" is a named cell containing the style you want to check against. Instead of your COUNTIF formula, try =Cellstylecount(B4:P53,Accent1) HTH, JP On Nov 14, 9:41 am, Gerard wrote: JP, I hate to abuse this site, but I think you would be the best one to ask since you created the function. I'm trying to get a count of various cells that contain particular styles. I tried this: "=COUNTIF(B4:P53,Cellstyle(Accent1))". I believe this is closer to correct (and I tried it as well): =COUNTIF(B4:P53,Cellstyle("Accent1")). They are returning 0 as the count. I'm not sure why though. Any ideas? Thanks again, Gerard "JP" wrote: I wrote a simple function to return the style of a given cell. Function Cellstyle(inp As Range) As String Cellstyle = inp.Style End Function Assume you have some data in cell A1. In B1 enter =Cellstyle(A1). HTH, JP On Nov 13, 4:13 pm, Gerard wrote: I'm trying to use a formula to determine the Cell (custom) Style? I believe that once I have this formula figured out I will be able to use it to "Count" the number of cells with a particular Style and display this. Any ideas? Thanks, Gerard- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to use a formula to determine the Cell Style?
Cheers, glad to help!
_.._..,_,_ ( ) ]~,"-.-~~[ .=])' (; ([ | ]:: ' [ '=]): .) ([ |:: ' | ~~----~~ (hope this comes out right) On Nov 14, 1:15 pm, Gerard wrote: JP, I vaguely understand this (the function). Indeed, it does work fine. I was easily able to modify to get all the "Accent#" counts in different cells. I owe you a beer - or a coffee - or whatever you would like to wet your whistle. Thank you very much for all the help. Much peace, Gerard |
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to use a formula to determine the Cell Style?
LMAO! It looks great!
Thanks again, Gerard "JP" wrote: Cheers, glad to help! _.._..,_,_ ( ) ]~,"-.-~~[ .=])' (; ([ | ]:: ' [ '=]): .) ([ |:: ' | ~~----~~ (hope this comes out right) On Nov 14, 1:15 pm, Gerard wrote: JP, I vaguely understand this (the function). Indeed, it does work fine. I was easily able to modify to get all the "Accent#" counts in different cells. I owe you a beer - or a coffee - or whatever you would like to wet your whistle. Thank you very much for all the help. Much peace, Gerard |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Req help on Formula or vb in excel to calculate between reference style and formula style tia sal2 | Excel Discussion (Misc queries) | |||
Req help on Formula or vb in excel to calculate between reference style and formula style tia sal2 | Excel Worksheet Functions | |||
how do I determine if a cell is used in a formula | Excel Discussion (Misc queries) | |||
can a1 reference style and r1c1 style be used in same formula? | Excel Worksheet Functions | |||
Is there a way to transfer a style by cell referrence in a formula | Excel Discussion (Misc queries) |