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

[Akash Maheshwari]

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

 

[Toppers]

this will count bold format in cells: modify to create a module for
strikethrough

(Rng.Font.strikethrough = True)

Place formula below in cell:

=CountBold(D1:BG1000)

To insert code:

Alt + f11

Alt+I

select Module

Copy/paste code

Function CountBold(InRange As Range) As Long
Dim Rng As Range
Application.Volatile True
For Each Rng In InRange.Cells
CountyBold = CountBold - (Rng.Font.Bold = True)
Next Rng

End Function

 

[Gord Dibben]

Akash

Here are a couple of UDF's you could use.

Function CountBold(rg As Range) As Long
'originally posted by Ron Rosenfeld
Dim c As Range
For Each c In rg
CountBold = CountBold - c.Font.Bold
Next c
End Function

Function CountStrike(rg As Range) As Long
'originally posted by Ron Rosenfeld
'revised by Gord Dibben
Dim c As Range
For Each c In rg
CountStrike = CountStrike - c.Font.Strikethrough
Next c
End Function

Usage is................=CountBold(range)


Gord Dibben MS Excel MVP

 

[Akash Maheshwari]

Hi Gord,

Its working fine but every time a value is entered in the related
range I need to refresh the formula...

It should be done autometically. Then whats the need of the macro.

it should be in the below mentioned way.

For Example Suppose we have:

Value in Cell B1 :20
Value in Cell C1 :20
Value in Cell D1 :20
Value in Cell E1 :20

and in Cell A1 there is a sum total =SUM(B1:E1)

Output would be 80

Now suppose i delete the value in E1

The output of cell A1 would automatically be 60. I dont have to
refresh the Cell A1.

But in the macro provided by you, i need to refresh the cell A1 every
time.

This needs to be rectified.

Awaiting for you mail.

Thanks

Akash Maheshwari

 

[Gord Dibben]

The UDF's count cells with strikethrough or bold formatting.

What has changing the value from 80 to 60 got to do with the above?

Maybe the bold or strikethrough is created by Conditional Formatting?

More detail please before we "rectify".


Gord

 

[Akash Maheshwari]

The UDF's count cells with strikethrough or bold formatting.

What has changing the value from 80 to 60 got to do with the above?

Maybe the bold or strikethrough is created by Conditional Formatting?

More detail please before we "rectify".

Gord

Hi Gord,

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

Function CountBold(rg As Range) As Long
'originally posted by Ron Rosenfeld
Dim c As Range
For Each c In rg
CountBold = CountBold - c.Font.Bold
Next c
End Function

Function CountStrike(rg As Range) As Long
'originally posted by Ron Rosenfeld
'revised by Gord Dibben
Dim c As Range
For Each c In rg
CountStrike = CountStrike - c.Font.Strikethrough
Next c
End Function


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.

This was the entire mystery.

Awaiting for the solution from ur end.

Thanks

Akash

 

[Gord Dibben]

CF Colors set by CF must be treated differently than regular color settings.

See Chip Pearson's site for info on this.

BTW........if your original posts had mentioned CF we could have saved you a lot
of time.

Gord