Home 
Search 
Today's Posts 
#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 
#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  
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) 