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

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

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

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

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

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

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 -

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 -

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 -

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 -

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 -

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

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