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.

 How to use a formula to determine the Cell Style?
 Author Name Remember Me? Password
 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
November 13th 07, 09:13 PM posted to microsoft.public.excel.misc
 Gerard external usenet poster Posts: 19
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
November 13th 07, 09:20 PM posted to microsoft.public.excel.misc
 JW[_2_] external usenet poster Posts: 638
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
November 13th 07, 09:30 PM posted to microsoft.public.excel.misc
 Gerard external usenet poster Posts: 19
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
November 13th 07, 10:14 PM posted to microsoft.public.excel.misc
 JP[_3_] external usenet poster Posts: 168
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
November 14th 07, 02:40 PM posted to microsoft.public.excel.misc
 Gerard external usenet poster Posts: 19
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
November 14th 07, 02:41 PM posted to microsoft.public.excel.misc
 Gerard external usenet poster Posts: 19
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
November 14th 07, 03:32 PM posted to microsoft.public.excel.misc
 JP[_3_] external usenet poster Posts: 168
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
November 14th 07, 03:33 PM posted to microsoft.public.excel.misc
 JP[_3_] external usenet poster Posts: 168
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
November 14th 07, 03:44 PM posted to microsoft.public.excel.misc
 Gerard external usenet poster Posts: 19
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
November 14th 07, 03:57 PM posted to microsoft.public.excel.misc
 JP[_3_] external usenet poster Posts: 168
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 Linear Mode

 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 User Control Panel Private Messages Subscriptions Who's Online Search Forums Forums Home Excel Newsgroups     Excel Discussion (Misc queries)     Setting up and Configuration of Excel     New Users to Excel     Excel Worksheet Functions     Links and Linking in Excel     Charts and Charting in Excel     Excel Programming About ExcelBanter     About this forum

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

All times are GMT +1. The time now is 09:31 PM.

 - Contact Us - ExcelBanter forum home - FAQ - Links - Privacy Statement - Top

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