Using VBA coding to count color Occurrences

Hi:

I am using Office (Word, Excel, and Powerpoint) 2007, and Windows 7, and  
below is what I'm trying to accomplish, hopefully, with VBA coding:

In Columns L & M I have dates that identify a beginning and end date for a 
class (L is "beginning" date and M is "end date") that a student has signed 
up for. If the student has not paid for their class, the class dates in 
Columns L & M are in "bold red font". Once payment has been made, the font 
changes from "bold red" to "unbold black" thanks to conditional formatting. 
This works just fine. However, I'm trying to accomplish two additional things 
that I'm having trouble with, and wasn't sure if VBA coding may be my answer:

Issue 1:

I'd like coding that gives a numerical outcome of how many clients, 
scheduled for a class have not yet paid for their class (in other words, 
count all bold red font in Column L as this would identify the number of 
individuals who haven't paid. If the font is anything but bold red, than this 
should not be counted, as the font color automatically turns to unbolded and 
black once a client is identified (via a specific date in Column K) as paid. 
This numerical outcome (clients who have not yet paid) should appear in cell 
K3.

Issue 2:

I'd like coding that merely gives me a flag which states: "Pending Balances" 
if the font color in color is Column L is red and bold. This flag should 
appear in cell L3.

Thanks very much for any help in advance!

Dan

0
Utf
4/22/2010 12:53:03 PM
excel.programming 6508 articles. 2 followers. Follow

4 Replies
898 Views

Similar Articles

[PageSpeed] 31

Since the color is coming from Conditional Formatting, then you have a 
formula relationship that evaluates to TRUE and FALSE (the one you used in 
the Conditional Formatting dialog box)... just use that formula relationship 
to create your count in K3 and to flag your cells in Column L.

-- 
Rick (MVP - Excel)



"Danny Boy" <DannyBoy@discussions.microsoft.com> wrote in message 
news:EE0E2A69-A815-4E61-80DD-8AE72345733E@microsoft.com...
> Hi:
>
> I am using Office (Word, Excel, and Powerpoint) 2007, and Windows 7, and
> below is what I'm trying to accomplish, hopefully, with VBA coding:
>
> In Columns L & M I have dates that identify a beginning and end date for a
> class (L is "beginning" date and M is "end date") that a student has 
> signed
> up for. If the student has not paid for their class, the class dates in
> Columns L & M are in "bold red font". Once payment has been made, the font
> changes from "bold red" to "unbold black" thanks to conditional 
> formatting.
> This works just fine. However, I'm trying to accomplish two additional 
> things
> that I'm having trouble with, and wasn't sure if VBA coding may be my 
> answer:
>
> Issue 1:
>
> I'd like coding that gives a numerical outcome of how many clients,
> scheduled for a class have not yet paid for their class (in other words,
> count all bold red font in Column L as this would identify the number of
> individuals who haven't paid. If the font is anything but bold red, than 
> this
> should not be counted, as the font color automatically turns to unbolded 
> and
> black once a client is identified (via a specific date in Column K) as 
> paid.
> This numerical outcome (clients who have not yet paid) should appear in 
> cell
> K3.
>
> Issue 2:
>
> I'd like coding that merely gives me a flag which states: "Pending 
> Balances"
> if the font color in color is Column L is red and bold. This flag should
> appear in cell L3.
>
> Thanks very much for any help in advance!
>
> Dan
> 
0
Rick
4/22/2010 2:05:06 PM
Do you have a formula suggestion to make this work Rick? I understand in 
theory what you are suggesting, and it makes sense, but I just can't figure 
out how to get Excel to count or flag based upon a bold red colored font. I'm 
still an Excel Newbie lol!

"Rick Rothstein" wrote:

> Since the color is coming from Conditional Formatting, then you have a 
> formula relationship that evaluates to TRUE and FALSE (the one you used in 
> the Conditional Formatting dialog box)... just use that formula relationship 
> to create your count in K3 and to flag your cells in Column L.
> 
> -- 
> Rick (MVP - Excel)
> 
> 
> 
> "Danny Boy" <DannyBoy@discussions.microsoft.com> wrote in message 
> news:EE0E2A69-A815-4E61-80DD-8AE72345733E@microsoft.com...
> > Hi:
> >
> > I am using Office (Word, Excel, and Powerpoint) 2007, and Windows 7, and
> > below is what I'm trying to accomplish, hopefully, with VBA coding:
> >
> > In Columns L & M I have dates that identify a beginning and end date for a
> > class (L is "beginning" date and M is "end date") that a student has 
> > signed
> > up for. If the student has not paid for their class, the class dates in
> > Columns L & M are in "bold red font". Once payment has been made, the font
> > changes from "bold red" to "unbold black" thanks to conditional 
> > formatting.
> > This works just fine. However, I'm trying to accomplish two additional 
> > things
> > that I'm having trouble with, and wasn't sure if VBA coding may be my 
> > answer:
> >
> > Issue 1:
> >
> > I'd like coding that gives a numerical outcome of how many clients,
> > scheduled for a class have not yet paid for their class (in other words,
> > count all bold red font in Column L as this would identify the number of
> > individuals who haven't paid. If the font is anything but bold red, than 
> > this
> > should not be counted, as the font color automatically turns to unbolded 
> > and
> > black once a client is identified (via a specific date in Column K) as 
> > paid.
> > This numerical outcome (clients who have not yet paid) should appear in 
> > cell
> > K3.
> >
> > Issue 2:
> >
> > I'd like coding that merely gives me a flag which states: "Pending 
> > Balances"
> > if the font color in color is Column L is red and bold. This flag should
> > appear in cell L3.
> >
> > Thanks very much for any help in advance!
> >
> > Dan
> > 
> .
> 
0
Utf
4/22/2010 5:20:01 PM
It would help if you tell us the formulas being used in the Conditional 
Formatting to color the cells red and black.

-- 
Rick (MVP - Excel)



"Danny Boy" <DannyBoy@discussions.microsoft.com> wrote in message 
news:3E905C7F-A4AD-4041-B3C4-32A533ADE345@microsoft.com...
> Do you have a formula suggestion to make this work Rick? I understand in
> theory what you are suggesting, and it makes sense, but I just can't 
> figure
> out how to get Excel to count or flag based upon a bold red colored font. 
> I'm
> still an Excel Newbie lol!
>
> "Rick Rothstein" wrote:
>
>> Since the color is coming from Conditional Formatting, then you have a
>> formula relationship that evaluates to TRUE and FALSE (the one you used 
>> in
>> the Conditional Formatting dialog box)... just use that formula 
>> relationship
>> to create your count in K3 and to flag your cells in Column L.
>>
>> -- 
>> Rick (MVP - Excel)
>>
>>
>>
>> "Danny Boy" <DannyBoy@discussions.microsoft.com> wrote in message
>> news:EE0E2A69-A815-4E61-80DD-8AE72345733E@microsoft.com...
>> > Hi:
>> >
>> > I am using Office (Word, Excel, and Powerpoint) 2007, and Windows 7, 
>> > and
>> > below is what I'm trying to accomplish, hopefully, with VBA coding:
>> >
>> > In Columns L & M I have dates that identify a beginning and end date 
>> > for a
>> > class (L is "beginning" date and M is "end date") that a student has
>> > signed
>> > up for. If the student has not paid for their class, the class dates in
>> > Columns L & M are in "bold red font". Once payment has been made, the 
>> > font
>> > changes from "bold red" to "unbold black" thanks to conditional
>> > formatting.
>> > This works just fine. However, I'm trying to accomplish two additional
>> > things
>> > that I'm having trouble with, and wasn't sure if VBA coding may be my
>> > answer:
>> >
>> > Issue 1:
>> >
>> > I'd like coding that gives a numerical outcome of how many clients,
>> > scheduled for a class have not yet paid for their class (in other 
>> > words,
>> > count all bold red font in Column L as this would identify the number 
>> > of
>> > individuals who haven't paid. If the font is anything but bold red, 
>> > than
>> > this
>> > should not be counted, as the font color automatically turns to 
>> > unbolded
>> > and
>> > black once a client is identified (via a specific date in Column K) as
>> > paid.
>> > This numerical outcome (clients who have not yet paid) should appear in
>> > cell
>> > K3.
>> >
>> > Issue 2:
>> >
>> > I'd like coding that merely gives me a flag which states: "Pending
>> > Balances"
>> > if the font color in color is Column L is red and bold. This flag 
>> > should
>> > appear in cell L3.
>> >
>> > Thanks very much for any help in advance!
>> >
>> > Dan
>> >
>> .
>> 
0
Rick
4/22/2010 5:49:23 PM
I'm using the formula you see below in Column L. I had preset the font to 
Bold and Red (which appears whenever I enter a date in Column L), and with 
the Conditional Formatting formula below, the font changes to unbolded black 
whenever a date is entered into Collumn K:

=ISNUMBER(K4)

"Rick Rothstein" wrote:

> It would help if you tell us the formulas being used in the Conditional 
> Formatting to color the cells red and black.
> 
> -- 
> Rick (MVP - Excel)
> 
> 
> 
> "Danny Boy" <DannyBoy@discussions.microsoft.com> wrote in message 
> news:3E905C7F-A4AD-4041-B3C4-32A533ADE345@microsoft.com...
> > Do you have a formula suggestion to make this work Rick? I understand in
> > theory what you are suggesting, and it makes sense, but I just can't 
> > figure
> > out how to get Excel to count or flag based upon a bold red colored font. 
> > I'm
> > still an Excel Newbie lol!
> >
> > "Rick Rothstein" wrote:
> >
> >> Since the color is coming from Conditional Formatting, then you have a
> >> formula relationship that evaluates to TRUE and FALSE (the one you used 
> >> in
> >> the Conditional Formatting dialog box)... just use that formula 
> >> relationship
> >> to create your count in K3 and to flag your cells in Column L.
> >>
> >> -- 
> >> Rick (MVP - Excel)
> >>
> >>
> >>
> >> "Danny Boy" <DannyBoy@discussions.microsoft.com> wrote in message
> >> news:EE0E2A69-A815-4E61-80DD-8AE72345733E@microsoft.com...
> >> > Hi:
> >> >
> >> > I am using Office (Word, Excel, and Powerpoint) 2007, and Windows 7, 
> >> > and
> >> > below is what I'm trying to accomplish, hopefully, with VBA coding:
> >> >
> >> > In Columns L & M I have dates that identify a beginning and end date 
> >> > for a
> >> > class (L is "beginning" date and M is "end date") that a student has
> >> > signed
> >> > up for. If the student has not paid for their class, the class dates in
> >> > Columns L & M are in "bold red font". Once payment has been made, the 
> >> > font
> >> > changes from "bold red" to "unbold black" thanks to conditional
> >> > formatting.
> >> > This works just fine. However, I'm trying to accomplish two additional
> >> > things
> >> > that I'm having trouble with, and wasn't sure if VBA coding may be my
> >> > answer:
> >> >
> >> > Issue 1:
> >> >
> >> > I'd like coding that gives a numerical outcome of how many clients,
> >> > scheduled for a class have not yet paid for their class (in other 
> >> > words,
> >> > count all bold red font in Column L as this would identify the number 
> >> > of
> >> > individuals who haven't paid. If the font is anything but bold red, 
> >> > than
> >> > this
> >> > should not be counted, as the font color automatically turns to 
> >> > unbolded
> >> > and
> >> > black once a client is identified (via a specific date in Column K) as
> >> > paid.
> >> > This numerical outcome (clients who have not yet paid) should appear in
> >> > cell
> >> > K3.
> >> >
> >> > Issue 2:
> >> >
> >> > I'd like coding that merely gives me a flag which states: "Pending
> >> > Balances"
> >> > if the font color in color is Column L is red and bold. This flag 
> >> > should
> >> > appear in cell L3.
> >> >
> >> > Thanks very much for any help in advance!
> >> >
> >> > Dan
> >> >
> >> .
> >> 
> .
> 
0
Utf
4/23/2010 2:51:04 PM
Reply:

Similar Artilces:

GetOpenFileName + Using own dialog template
Hi. I have a question: I am using the GetOpenFileName function to display the Windows FileDialog. I want to use the EXPLORER functionality and therefore I declared the flag OFN_EXPLORER in the OPENNAME-structure. Now here is the problem: I want to use my own dialog-template instead of the standard-file-open dialog. So i wrote my own template and set the attribute templateName of the OPENNAME structur to my own template. Furthermore i activated the flag OFN_ENABLETEMPLATE. But when I call the GetOpenFileName function with these properties I get the errorcode CDERR_GENERALCODES and the dialog is...

Continuous Form Use
I created a form similar to an Access form. It's for logging in phone calls and messages. Is there a way to set it up like in Access where a single form appears on 1 page but can generate subsequent pages within the file? Or is setting it up as a table the only way it can be done? Our IT contractor doesn't want me to use Access otherwise this would be a breeze. Any suggestions or help is appreciated. Thanks. You can spawn forms off a form, and access the data on that form. -- HTH RP (remove nothere from the email address if mailing direct) "ryjack" <ryjack@disc...

Comments using Validation input
A few days ago a question relating to comments grabbed my attention. The reply informed us that the comment in question was able to be seen by clicking the cell it was attached to and as long as that cell remained active the comment could be seen. So we have a comment which is not a comment visible only when the cell is selected as opposed to a comment which is visible when the cursor is over the cell. Sounds confusing but it achieves the result of showing a "floating" comment which remains in the same place on the screen even when it applies to a cell in a 'frozen' section a...

Order Count
I have a customer sales database. I use Excel pivot tables to analyze it (external data source in Excel). I want to count the amount of orders but the formula in the pivot table gives me the count of all the items including all the products in each order, if the order has different ship dates and things like that. How can I obtain a figure which represent the amount of all the new orders placed in each month? Excel related solutions go back to the source table. So I figure the solution must be in Access. How can I do that? Thank you! On Tue, 11 Dec 2007 12:32:01 -0800, FA ...

Receivable transactions using Integration Manager
Hi, I'm new to Integration manager and while trying to import receivable transactions of type "invoice" into Great Plains using Integration Manager, I receive the following errors: ERROR: Field 'Customer ID' does not have a default value. ERROR: Field 'Distribution.Distribution Account' does not have a default value. I am using a tab delimited text file for import. Customer Id is of the format 000001 Distr. Account is of the format 9999-999-99-99-99 Can anyone help me with this. Check each one of your mapping and make sure it says 'use source' an...

multiple colors on format of plot area
How can I change the format of the plot area to multiple colors. for example I would like to have a Bar Chart with the background plot area starting @ blue , then Green, then Red. Then have the temperature bars plot over this area. thanks You can do so by bringing a stacked column chart into the mix. Please see Jon's example below: http://peltiertech.com/Excel/Charts/ColoredQuadrantBackground.html -- John Mansfield http://cellmatrix.net "BillO" wrote: > How can I change the format of the plot area to multiple colors. > > for example I would like to have a ...

Using cell reference with logical operator in DGET expression
I am using DGET to search an array in the worksheet to find a particular percent to use in a calculation elsewhere in the spreadsheet. The row members of the array contain a series of from and to values that I use to identify which row has the percent I am looking for. I have defined the array as a range and I can use the logical operatirs with numeric values in the range criteria and everything works fine. For example, I can use <500 in the appropriate cell in range criteria to find the percent to use when the value for that column in the array is less than 500. All working f...

can I get "step by step" on color seperating for commercial printi
This is kind of an extension on my previous message but I feel it would help a lot more. Can someone give me a quick step by step on making a document with text and photo into a press quality color seperated piece using PMS colors and process black? Or maybe point me to a site that can give step by step on that stuff? ...

how i can change default file format Outlook uses to save message
I my saving message in my inbox using "File->Save As" menu. "Save As.." dialog by default shows "Save As Type:" as "HTML". How I can change default file format Outlook uses to save messages to as "Outlook Message Format - Unicode" ?????? Rajkumar <Rajkumar@discussions.microsoft.com> wrote: > I my saving message in my inbox using "File->Save As" menu. > "Save As.." dialog by default shows "Save As Type:" as "HTML". > How I can change default file format Outlook uses to save messages...

Help on subscript out of range error (VB6/VBA)
Hi, I want to work with multiple excel workbooks en worksheets using a stand alone VB6 application. 1. First I open and close the workbooks and populate a listbox with sheetnames. 2. Then I select some sheets and fill an Array. (This array contains sheetnames from different workbooks) 3. The last step is to open and close the workbooks again and loop (For Each) through the sheets to get the information I need using the above mentioned array. The only problem is that I receive an "Subscript out of Range" error. This error occurs because I'm attempting tot access a workshee...

Have VB Code "on" for all workbooks
I have the following code in my personal workbook. How can I get the VB code to work in all workbooks or do I have to make an add-in? Private Sub Worksheet_SelectionChange(ByVal Target As Range) Application.EnableEvents = False Union(Target.EntireColumn, Target.EntireRow).Select 'Target.EntireRow.Select Target.Activate Application.EnableEvents = True End Sub Also, can you change the color? Thanks Hi, I'd go with Chip Pearson's rowliner addin http://www.cpearson.com/excel/rowliner.htm -- Mike When competing hypotheses are otherwise equal, adopt the hypot...

How to show a count of Unique IDs in a Pivot Table
Is there a way to use Pivot tables in a way that shows a count of Unique IDs (over time). The problem I am having in using the Pivot table to show trended data, is that the "Count" option produces a count of each record that has any information, when oftentimes I simply need a count of unique IDs. So the below Column would have a count of 3 Names, when I prefer for it to produce a count of 1 Name. Any recommendations would be appreciated..... Name --------- David David David Excel 2007 PivotTable Count Unique IDs http://www.mediafire.com/file/dgmmaznjyy2/06_21_09.xlsx Thanks ve...

No fill color visible...not high contrast!
Cell fill colors are not visible in Excel. This only occurs in Excel. The high resolution box is not checked. Just for fun I checked it. That would not cause an Excel problem that would cause an everything problem. It is only a problem when I am logged in. Other users do not have the problem. Any other suggestions? ...

Word 2003 VBA to limit options in Inputbox to those in drop down
I have the following macro which works great to make a form drop down list mandatory to fill in: Sub MustFillIn() If ActiveDocument.FormFields("nameDD").Result = "ENTER NAME" Then Do sInFld = InputBox("This field must be filled in, fill in below.") Loop While sInFld = "" ActiveDocument.FormFields("nameDD").Result = sInFld End If End Sub However, the user is free to enter whatever they want in the input box, although the field this fires from if nothing is entered is a drop down- is there any way to ...

External data query using MAX/SUM
I'm trying to create an external data link using Microsoft Query. Ho do I write SQL in Microsoft query that will return a max date and su an amount field for the max date only? Current SQL is as follows: SELECT DISTINCT CB_PORTFOLIO_DMN.PORTFOLIO_NAME Max(CB_POOL_PORTFOLIO.POST_DATE), Sum(CB_POOL_PORTFOLIO.EXPOSURE_AMT) Count(CB_POOL_PORTFOLIO.NBR_AVG_MATY_YRS) FROM CB06U.CB_POOL_PORTFOLIO CB_POOL_PORTFOLIO, CB06U.CB_PORTFOLIO_DM CB_PORTFOLIO_DMN WHERE CB_PORTFOLIO_DMN.PORTFOLIO_ID = CB_POOL_PORTFOLIO.PORTFOLIO_ID GROUP BY CB_PORTFOLIO_DMN.PORTFOLIO_NAME This returns the sum of the exp...

Howto not print a background color
In my wrksht a cell range is highlighted as an indicator to various users, the highlight is a background autofill color. I would like to print with out this background color subject in the printed version. Thanks---Jeff ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ ~~Now Available: Financial Statements.xls, a step by step guide to creating financial statements Jeff File>Page Setup>Sheet Checkmark "Black and White". Gord Dibben XL2002 On Wed, 3 ...

List of fonts used in a Word doc -- the easy way
The VBA scripts that I have found all implement a character-by-character scan throu= gh the document, which can take a long time in later versions of Word. You can blame Wor= d's "formatting" feature for the slowdown. There=E2=80=99s a faster way: Save the document in .rtf format, then open the .rtf fi= le in a text editor such as notepad.exe. The fonts are listed in a font table ( {\fonttb= l ) located at the very beginning of the file. Each font entry looks something like thi= s: = {\f0\froman\fprq2\fcharset0 Times New Roman;} Any entries with a character set ...

How to Concatenate a variable name in a vba loop ?
Const Value1 as long = 5 Const Value2 as long = 12 Const Value3 as long = 7 For i = 1 to 3 MsgBox Value & i '<-- How to do this properly? Next i I want MsgBox to display 5 then 12 then 7 I can't figgure out how to get Value and i to combine properly to do this. thanks for any help. PS - This is a repost as I didn't ask the question correctly last time. But you are happy with the last answer you got? -- HTH RP (remove nothere from the email address if mailing direct) "tmb" <topmailbox@yahoo.com> wrote in message news:u%d2e.8729$vd.6624@tornad...

sending email from outlook using AOL
After much exasperation have finally got outlook to send email whilst using my AOL internet connection. The issue is that I have to have my aol email address entered in the Outlook email box but I use my outlook email address in the reply email address box. The problem is that any recipient of an email from outlook will see my aol email address in the from box and if they click on reply it will be sent to my aol email box and not to my outlook email box. If my aol email address is not entered, in the Outlook email box, the email is rejected by aol. Using AOL 9 & Outlook 2002 Any ...

question about a code to "save as"
i have a command button on a spread sheet that, when clicked, will sav the spread sheet in a folder and name it, depending what is in certai cells. my problem is where i want it saved. right now, i have all spread sheets being saved into one certai folder, but i would like the spread sheet to be saved into a folde that is specified in cell d2. this is the code i have now. Sub Save_As_FileName() FName1 = Range("d2").Value FName2 = Range("d3").Value FName3 = Range("d5").Value FName4 = Range("d6").Value Fname5 = Range("d7").Value pth = "f:...

color tabs
Hi all, any idea on how to put color to your tabs (subsheets) ? TIA DANIEL In Excel 2002, right-click on a sheet tab, and choose 'Tab Color' If you have an earlier version of Excel, the tabs can't be coloured. news.global.co.za wrote: > Hi all, any idea on how to put color to your tabs (subsheets) ? > > TIA > > DANIEL -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html Thanks, yip I'll have to upgrade office97 to office xp then DAN "Debra Dalgleish" <dsd@contextures.com> wrote in message news:...

Count How Many Emails Were Outgoing in a Day?
Thanks in advance. How can we count outgoing emails from our server, excluding emails with a destination to our own domain? The log in SMTP log files only shows incoming connections. -- Mark Simmerman Napa, California, USA ...

Conditional Summing across ranges using arrays
Hi all - I'm struggling to find a formula that will solve my problem: I have a matrix of numbers defined by a series of numerical column and row headers. RefCel 1 2 3 4 5 . . . m 1 a # # # # 2 # # # # # .. 5 b # # # # .. n # # # # # What I'm trying to do is for a give column header and a subset of row headers, sum the intersecting values. Let's assume that the subset are held in a named range Subset and the row and column headers are in range called RowHead and ColHead and...

Music files using "Package for CD" Option in Powerpoint
I am trying to burn my show onto a disk, and the show has songs linked to it. I know the songs are not embedded into the show, so I was looking for a solution for this. I saw the "package for CD" option in the menu and according to that, it is supposed to take your show, and all linked files and burn it onto a disk to be played back using powerpoint viewer. I made sure the box was checked to include linked files. After the disk burned, no music. I tried putting all the music and show in one folder and then running the package for cd tool, still no music. Is ther...

Using a txt string in formulae
Right this is an anoying little beggar but probably simple and im failing to see the wood from the trees so to speak ! right i have a workbook with multiple sheets and a front sheet the front sheet reports results of counts from the other sheets using =COUNT(sheet1!A:A) and =COUNTIF(Sheet1E:E,"Neg room") and other similair formula my question is this In column A i have the name of the sheet so I.E. A1=sheet1 which hyperlinks to the named sheet and columb B has the first formulae and so on and so on! my question is is there a way i can add the Word name of the sheet the Count form...