Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 19
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 638
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 19
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 168
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 19
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 19
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 168
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 168
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 19
Default 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   Report Post  
Posted to microsoft.public.excel.misc
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 -





  #11   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 19
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 168
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 19
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


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 07: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 07:08 PM


All times are GMT +1. The time now is 08:43 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"