A Microsoft Excel forum. ExcelBanter

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.

Go Back   Home » ExcelBanter forum » Excel Newsgroups » Excel Discussion (Misc queries)
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

How to use a formula to determine the Cell Style?



 
 
Thread Tools Display Modes
  #1  
Old November 13th 07, 10:13 PM posted to microsoft.public.excel.misc
Gerard
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
Ads
  #2  
Old November 13th 07, 10:20 PM posted to microsoft.public.excel.misc
JW[_2_]
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  
Old November 13th 07, 10:30 PM posted to microsoft.public.excel.misc
Gerard
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  
Old November 13th 07, 11:14 PM posted to microsoft.public.excel.misc
JP[_3_]
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  
Old November 14th 07, 03:40 PM posted to microsoft.public.excel.misc
Gerard
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  
Old November 14th 07, 03:41 PM posted to microsoft.public.excel.misc
Gerard
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  
Old November 14th 07, 04:32 PM posted to microsoft.public.excel.misc
JP[_3_]
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  
Old November 14th 07, 04:33 PM posted to microsoft.public.excel.misc
JP[_3_]
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  
Old November 14th 07, 04:44 PM posted to microsoft.public.excel.misc
Gerard
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  
Old November 14th 07, 04:57 PM posted to microsoft.public.excel.misc
JP[_3_]
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 -



 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

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 11:19 PM.


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