View Single Post
  #10   Report Post  
Posted to microsoft.public.excel.misc
JP[_3_] JP[_3_] is offline
external usenet poster
 
Posts: 168
Default 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 -