Counting Cells which have Bold & Red Strike Effect Through Conditional Formatting



Hi Gary,

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

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

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
Column A is to count no of cells with Bold Fonts
Column B is to count no of cells with Red Strike Through Effect

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
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
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
Its the conditional formatting doing that manually.

What could be done to solve this query.

Awaiting for your mail.


Akash Maheshwari


Hi Garry,

I am not getting the same thing which i wanted from the site address
given by you.

More help is required in regard to subject matter.


Akash Maheshwari

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question