Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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.misc
|
|||
|
|||
How to count number of Cell have Strike Through Effect & Bold as f
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 "Akash Maheshwari" 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.misc
|
|||
|
|||
How to count number of Cell have Strike Through Effect & Bold as font style.
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 On Wed, 27 Jun 2007 13:04:01 -0000, Akash Maheshwari 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.misc
|
|||
|
|||
How to count number of Cell have Strike Through Effect & Bold as font style.
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 On Jun 28, 1:56 am, Gord Dibben <gorddibbATshawDOTca wrote: 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 On Wed, 27 Jun 2007 13:04:01 -0000,AkashMaheshwari 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 AkashMaheshwari |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to count number of Cell have Strike Through Effect & Bold as font style.
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 On Thu, 28 Jun 2007 08:36:01 -0000, Akash Maheshwari wrote: 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 On Jun 28, 1:56 am, Gord Dibben <gorddibbATshawDOTca wrote: 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 On Wed, 27 Jun 2007 13:04:01 -0000,AkashMaheshwari 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 AkashMaheshwari |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to count number of Cell have Strike Through Effect & Bold as font style.
On Jun 28, 7:28 pm, Gord Dibben <gorddibbATshawDOTca wrote:
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 On Thu, 28 Jun 2007 08:36:01 -0000,AkashMaheshwari wrote: 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 AkashMaheshwari On Jun 28, 1:56 am, Gord Dibben <gorddibbATshawDOTca wrote: 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 On Wed, 27 Jun 2007 13:04:01 -0000,AkashMaheshwari 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 AkashMaheshwari 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),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 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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to count number of Cell have Strike Through Effect & Bold as font style.
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 On Fri, 29 Jun 2007 04:37:49 -0000, Akash Maheshwari wrote: On Jun 28, 7:28 pm, Gord Dibben <gorddibbATshawDOTca wrote: 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 On Thu, 28 Jun 2007 08:36:01 -0000,AkashMaheshwari wrote: 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 AkashMaheshwari On Jun 28, 1:56 am, Gord Dibben <gorddibbATshawDOTca wrote: 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 On Wed, 27 Jun 2007 13:04:01 -0000,AkashMaheshwari 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 AkashMaheshwari 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),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 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
frame style effect required | Excel Discussion (Misc queries) | |||
How to use DOUBLE striketrough effect on a font in Excel | Excel Discussion (Misc queries) | |||
How can i test a cell for bold style in a cell (Excel 2003)? | Excel Worksheet Functions | |||
Can you keep a cells BOLD Font, cell color size | Excel Discussion (Misc queries) | |||
how do i get font style back on toolbar? | Excel Discussion (Misc queries) |