About Us

Gary Keramidas

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
(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

Thread Tools	Search this Thread
Show Printable Version	Search this Thread: Advanced Search
Display Modes
Switch to Linear Mode Hybrid Mode Switch to Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
why does it only bold the number in red when i apply the % style?	senorita2009	Excel Discussion (Misc queries)	2	October 5th 09 06:31 PM
if font is bold then cell value is number	Jon	Excel Discussion (Misc queries)	1	September 13th 08 11:50 AM
How to count number of Cell have Strike Through Effect & Bold as font style.	Akash Maheshwari	Excel Discussion (Misc queries)	6	June 29th 07 09:39 PM
frame style effect required	[email protected]	Excel Discussion (Misc queries)	5	November 30th 06 04:46 PM
How can i test a cell for bold style in a cell (Excel 2003)?	Mike A.	Excel Worksheet Functions	2	March 6th 06 07:23 PM

Menu

About Us