If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. 


Thread Tools  Display Modes 
#1




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 
Ads 
#2




How to use a formula to determine the Cell Style?
By "style" do you mean color? If so, Chip Pearson has some great
writeups 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




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 > writeups 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




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




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




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




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




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




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




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  
Thread Tools  
Display Modes  


Similar Threads  
Thread  Thread Starter  Forum  Replies  Last Post 
Req help on Formula or vb in excel to calculate between reference style and formula style tia sal2  temp  Excel Discussion (Misc queries)  1  September 13th 07 09:02 AM 
Req help on Formula or vb in excel to calculate between reference style and formula style tia sal2  temp  Excel Worksheet Functions  1  September 13th 07 09:02 AM 
how do I determine if a cell is used in a formula  razorsharp  Excel Discussion (Misc queries)  1  December 20th 06 06:53 PM 
can a1 reference style and r1c1 style be used in same formula?  rjagga  Excel Worksheet Functions  1  September 17th 06 10:58 AM 
Is there a way to transfer a style by cell referrence in a formula  Binki55_CanYouHearMeNow  Excel Discussion (Misc queries)  1  November 30th 04 06:08 PM 