How to use a formula to determine the Cell Style?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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
 
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
 
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,
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
 
Glad to help!

--JP


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







- Show quoted text -
Click to expand...
 
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
 
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
 
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
 
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
 
Cheers, glad to help!
_.._..,_,_
( )
]~,"-.-~~[
.=])' (; ([
| ]:: ' [
'=]): .) ([
|:: ' |
~~----~~
(hope this comes out right)
 
LMAO! It looks great!

Thanks again,
Gerard


JP said:
Cheers, glad to help!
_.._..,_,_
( )
]~,"-.-~~[
.=])' (; ([
| ]:: ' [
'=]): .) ([
|:: ' |
~~----~~
(hope this comes out right)


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
Click to expand...
Click to expand...
 
Back
Top