How to count number of Cell have Strike Through Effect & Bold as font style.

[Akash]

Hi,

I have data in Column D:BG

Mostly Cell have Strike Through Effect & Bold as font style.

How can we count those cells which are having strike Through Effect in
it.
How can we count those cells which are having Bold Font Style in It.

Awaiting for your reply.

Thanks

Akash Maheshwari

 

[Susan]

here's a link to an old post that discussed this issue

http://groups.google.com/group/micr...ount+bold+cells&rnum=1&hl=en#dfb571acf6b34517

==========================================================
Newsgroups: microsoft.public.excel.programming
From: John Green <[email protected]>
Date: 1998/12/23
Subject: Re: Function to count bold cells

Try the following:

Public Function CountBold(myRange As Range) As Long
Dim c As Range, count As Long


For Each c In myRange
If c.Font.Bold = True Then
count = count + 1
End If
Next c
CountBold = count
End Function
========================================

i don't know if they ever got it to work, though; they didn't post a
final code

susan

 

[Mike H]

Try this, it works on the active worksheet

Sub stantial()
Dim myRange As Range
Set myRange = Range("a1:c10") '< Alter to suit
For Each c In myRange
c.Select
If Selection.Font.Strikethrough = True Then
strike = strike + 1
ElseIf Selection.Font.Bold = True Then Bold = Bold + 1
End If
Next
MsgBox (strike & " Strikethrough cells " & Bold & " Bold cells")

End Sub

Mike

 

[Gary Keramidas]

mike:

i just point this out because i've seen so many posts from the pros about not
having to select cells most of the time, and so i don't.
you can also do this without selecting anything. not criticizing you, i see you
help a lot of people

like i mentioned, just point this out.(promise i won't do it again<g>)


Sub stantial()
Dim myRange As Range
Set myRange = Range("a1:c10") '< Alter to suit
For Each c In myRange

If c.Font.Strikethrough = True Then
strike = strike + 1
ElseIf c.Font.Bold = True Then Bold = Bold + 1
End If
Next
MsgBox (strike & " Strikethrough cells " & Bold & " Bold cells")

End Sub

 

[Akash]

mike:

i just point this out because i've seen so many posts from the pros about not
having to select cells most of the time, and so i don't.
you can also do this without selecting anything. not criticizing you, i see you
help a lot of people

like i mentioned, just point this out.(promise i won't do it again<g>)

Sub stantial()
Dim myRange As Range
Set myRange = Range("a1:c10") '< Alter to suit
For Each c In myRange

If c.Font.Strikethrough = True Then
strike = strike + 1
ElseIf c.Font.Bold = True Then Bold = Bold + 1
End If
Next
MsgBox (strike & " Strikethrough cells " & Bold & " Bold cells")

End Sub

Hi,

Thanks for the help but I have not yet received any solution for my
querry. i think u have not got my point. Let me explain you more
precisely.

Column A is to count no of cells with Bolds. (so formula is required)
Column B is to count no of cells with Red Strike Through Effect (so
formula is required)

I have data in Column E5 : BH5

So cell A5 would count no of cells with bold fonts in cell E5 : BH5
And cell B5 would count no of cells with Red Strike Through Effect in
cell E5 : BH5

Awaiting for you mail.

Thanks

Akash Maheshwari

 

[NickHK]

Hi,

Thanks for the help but I have not yet received any solution for my
querry. i think u have not got my point. Let me explain you more
precisely.

Column A is to count no of cells with Bolds. (so formula is required)
Column B is to count no of cells with Red Strike Through Effect (so
formula is required)

I have data in Column E5 : BH5

So cell A5 would count no of cells with bold fonts in cell E5 : BH5
And cell B5 would count no of cells with Red Strike Through Effect in
cell E5 : BH5

Awaiting for you mail.

Thanks

Akash Maheshwari

And the replies you have received give you no ideas ?

NickHK

 

[Gary Keramidas]

you can give this a try, just change the sheet name

Option Explicit

Sub stantial()
Dim ws As Worksheet
Set ws = Worksheets("Sheet1")
Dim myRange As Range, c As Range
Dim i As Long
ws.Range("a5:B5").Clear
Set myRange = ws.Range("e5:BH5") '< Alter to suit
For Each c In myRange
If c.Font.Strikethrough = True And c.Font.ColorIndex = 3 Then
ws.Range("B5").Value = ws.Range("B5").Value + 1
ElseIf c.Font.Bold = True Then
ws.Range("A5").Value = ws.Range("A5").Value + 1
End If
Next
End Sub

 

[Gary Keramidas]

guess they just wanted the solution!

--


Gary

And the replies you have received give you no ideas ?

NickHK

 

[Susan]

Column A is to count no of cells with Bolds. (so formula is required)

you can't do it with a formula. has to be a macro.

susan

 

[Akash]

Hi Gary,

I use conditional formatting in my sheet. Let me explain you the
entire sheet:

E F G
Time In | Time Out | Time In
9:30AM | 8:25 PM | 9:31 AM

Members working late after 20:30hrs will be given the permission to
come to work the next day by 10:30hrs.

So in Column F i have placed only one condition that if the employee
works after 20:30hrs make the font bold otherwise regular fonts.

Our company policy is:

If worked till 20:29hrs then the employee is allowed to resume his
work by 9:30hrs next day, if after 9:30hrs it should be Red in color.

if worked till 20:30hrs then the employee is allowed to resume his
work by 10:30hrs next day. if after 10:30hrs it should be red in
color.

So i used this conditions in my sheet.

n column G, cell G5, conditional formatting for Condition 1:
Formula is: =AND(F5<TIME(20,29,0),G5>TIME(9,30,0))

Condition 2 in G5 is:
Formula is: =AND(F5>=TIME(20,29,0),G5>TIME(10,30,0))

For column F , cell F5 conditional formatting is:
Cell Value is | Greater Than | 0.853472222222222 .

Its working fine up to this

Now my question was how to count no of cells with Bolds.& Red Strike
Through Effect
(so formula is required)
Column A is to count no of cells with Bold Fonts (so formula is
required)
Column B is to count no of cells with Red Strike Through Effect (so
formula is required)

I have data in Column E5 : BH5

So cell A5 would count no of cells with bold fonts in cell E5 : BH5
And cell B5 would count no of cells with Red Strike Through Effect in
cell E5 : BH5

U had given me the resolution

Sub stantial()
Dim ws As Worksheet
Set ws = Worksheets("Time Token Sheet")
Dim myRange As Range, c As Range
Dim i As Long
ws.Range("a5:B30").Clear
Set myRange = ws.Range("e5:BH30") '< Alter to suit
For Each c In myRange
If c.Font.Strikethrough = True And c.Font.ColorIndex = 3 Then
ws.Range("B5:B30").Value = ws.Range("B5").Value + 1
ElseIf c.Font.Bold = True Then
ws.Range("A5:A30").Value = ws.Range("A5").Value + 1
End If
Next
End Sub

It is working a bit fine but as i had used Conditional Formatting so
we don't have physically Bold Fonts or Red Strike Through Effect
fonts.
Its the conditional formatting doing that manually.

What could be done to solve this query.

Awaiting for your mail.

Thanks Akash Maheshwari

 

[Gary Keramidas]

check chip's site
http://www.cpearson.com/excel/CFColors.htm