Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to count number of Cell have Strike Through Effect & Bold as font style.
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to count number of Cell have Strike Through Effect & Bold as font style.
here's a link to an old post that discussed this issue
http://groups.google.com/group/micro...b571acf6b34517 ================================================== ======== Newsgroups: microsoft.public.excel.programming From: John Green Date: 1998/12/23 Subject: 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 On Jun 27, 9:03 am, Akash wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to count number of Cell have Strike Through Effect & Bold as f
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 "Akash" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to count number of Cell have Strike Through Effect & Bold as f
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 -- Gary "Mike H" wrote in message ... 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 "Akash" wrote: 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to count number of Cell have Strike Through Effect & Bold as f
On Jun 27, 6:44 pm, "Gary Keramidas" <GKeramidasATmsn.com wrote:
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 -- Gary "Mike H" wrote in message ... 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 "Akash" wrote: 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 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to count number of Cell have Strike Through Effect & Bold as f
"Akash"
egroups.com... On Jun 27, 6:44 pm, "Gary Keramidas" <GKeramidasATmsn.com wrote: 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 -- Gary "Mike H" wrote in message ... 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 "Akash" wrote: 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 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to count number of Cell have Strike Through Effect & Bold as f
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 "Akash" wrote in message oups.com... On Jun 27, 6:44 pm, "Gary Keramidas" <GKeramidasATmsn.com wrote: 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 -- Gary "Mike H" wrote in message ... 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 "Akash" wrote: 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 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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to count number of Cell have Strike Through Effect & Bold as f
guess they just wanted the solution!
-- Gary "NickHK" wrote in message ... "Akash" egroups.com... On Jun 27, 6:44 pm, "Gary Keramidas" <GKeramidasATmsn.com wrote: 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 -- Gary "Mike H" wrote in message ... 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 "Akash" wrote: 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 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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to count number of Cell have Strike Through Effect & Bold as f
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 On Jun 27, 10:09 am, Akash wrote: On Jun 27, 6:44 pm, "Gary Keramidas" <GKeramidasATmsn.com wrote: 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 -- Gary "Mike H" wrote in message ... 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 "Akash" wrote: 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 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- Hide quoted text - - Show quoted text - |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to count number of Cell have Strike Through Effect & Bold as f
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 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to count number of Cell have Strike Through Effect & Bold as f
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
why does it only bold the number in red when i apply the % style? | Excel Discussion (Misc queries) | |||
if font is bold then cell value is number | Excel Discussion (Misc queries) | |||
How to count number of Cell have Strike Through Effect & Bold as font style. | Excel Discussion (Misc queries) | |||
frame style effect required | Excel Discussion (Misc queries) | |||
How can i test a cell for bold style in a cell (Excel 2003)? | Excel Worksheet Functions |