Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using VBA coding to count color Occurrences
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" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using VBA coding to count color Occurrences
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" wrote in message ... 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 . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using VBA coding to count color Occurrences
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" wrote in message ... 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" wrote in message ... 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 . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using VBA coding to count color Occurrences
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" wrote in message ... 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" wrote in message ... 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 . . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count occurrences when two conditions are met | Excel Discussion (Misc queries) | |||
Count how many occurrences | Excel Discussion (Misc queries) | |||
How do I count repeat Occurrences | Excel Discussion (Misc queries) | |||
trying to COUNT occurrences when certain criteria is met | Excel Discussion (Misc queries) | |||
Count unique occurrences of name | Excel Discussion (Misc queries) |