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:

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),G5TIME(9,30,0))

Condition 2 in G5 is:
Formula is: =AND(F5=TIME(20,29,0),G5TIME(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
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

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

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

--


Gary


"Akash" wrote in message
oups.com...
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),G5TIME(9,30,0))

Condition 2 in G5 is:
Formula is: =AND(F5=TIME(20,29,0),G5TIME(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
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

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

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.

Thanks

Akash Maheshwari

On Jun 28, 10:28 am, "Gary Keramidas" <GKeramidasATmsn.com wrote:
check chip's sitehttp://www.cpearson.com/excel/CFColors.htm

--

Gary

"Akash" wrote in message

oups.com...

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),G5TIME(9,30,0))

Condition 2 in G5 is:
Formula is: =AND(F5=TIME(20,29,0),G5TIME(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
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