I'm a novice. I've developed a crude, yet effective,
spreadsheet that assigns client job numbers, identifies
client name, job description, contact, etc.

I keep track of the progress and status of each job number
by "Highlighting" particular cells as tasks are
accomplished, I.E., Quote Submittal would be indicated by
highlighting the Job Number cell and Quote Approval would
be indicated by highlighting the Client name and so on.

In the last column, I enter the dollar amount of each job
number. I set up a "Running Total" or "Tally" of the
amounts listed in the last column. When the job is paid
in full, I highlight it to indicate such. I would like
the "Tally" formula to keep a running total of only the
highlighted amounts in the last column. (Job Numbers that
have been paid in full).

Is it possible to include color code as a factor to be
used in the calculations of the formula? If yes, how?

Thanks much for any help on this issue.

To my knowledge without writing VBA code, however you can create conditonal
summations use SUMIF that could test the condition of a value in a column
for each row of data. This same column could be used to control the colour
of cells using conditional formatting. If you are colour coding cells based
on data values this could be the same values used for the conditional SUMIF
command.

Cheers
Nigel


"Rocco" wrote in message
...
I'm a novice. I've developed a crude, yet effective,
spreadsheet that assigns client job numbers, identifies
client name, job description, contact, etc.

I keep track of the progress and status of each job number
by "Highlighting" particular cells as tasks are
accomplished, I.E., Quote Submittal would be indicated by
highlighting the Job Number cell and Quote Approval would
be indicated by highlighting the Client name and so on.

In the last column, I enter the dollar amount of each job
number. I set up a "Running Total" or "Tally" of the
amounts listed in the last column. When the job is paid
in full, I highlight it to indicate such. I would like
the "Tally" formula to keep a running total of only the
highlighted amounts in the last column. (Job Numbers that
have been paid in full).

Is it possible to include color code as a factor to be
used in the calculations of the formula? If yes, how?

Thanks much for any help on this issue.




----== Posted via Newsfeed.Com - Unlimited-Uncensored-Secure Usenet News==----
http://www.newsfeed.com The #1 Newsgroup Service in the World! 100,000 Newsgroups
---= 19 East/West-Coast Specialized Servers - Total Privacy via Encryption =---

I would like to know something simular too.
Sorry to burst in on your question.......but


I'm in the process of writing a VBA program, and i cannot figure out :
if i give a range of data, how can i get an average on only the cells
with black or default font color, because i have the program hilighting
some values in color and do not want these values included in the
average... this would be included inside one of my loops because i need
it to do each line individually.

thanks guys.


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/

Check out this webpage
http://www.cpearson.com/excel/colors.htm

--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)
www.rondebruin.nl



"chick-racer" wrote in message ...

I would like to know something simular too.
Sorry to burst in on your question.......but


I'm in the process of writing a VBA program, and i cannot figure out :
if i give a range of data, how can i get an average on only the cells
with black or default font color, because i have the program hilighting
some values in color and do not want these values included in the
average... this would be included inside one of my loops because i need
it to do each line individually.

thanks guys.


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/

actually i have, and cannot get it to work for me.. i'll show you a tiny
slice of my code. i have an endless loop and am very very stuck.

I believe it's the "lowval" that i need to ensure only looks at black
or default color font.
I will then add code to make it find average of the cells with default
font using the same principles.


grubb = Cells(J + K, "V").value
tlow = Cells(J + K, "T").value
thigh = Cells(J + K, "U").value

While tlow grubb

Set rng = Range("D" & J + K, "N" & J + K)
lowval = Application.WorksheetFunction.Min(rng) 'must
only get lowval that is black or default color
For Each newrng In rng.Cells 'both
rng and newrng are set as Range
If newrng.Font.ColorIndex = xlAutomatic Or
newrng.Font.ColorIndex = 1 Then
result = Application.Match(lowval, rng, 0)
If Not IsError(result) Then rng(1,
result).Font.Color = RGB(0, 10, 200)
End If
Next newrng

'update tlow for the 'while' test
tlow = (Cells(J + K, "R").value - lowval) /
(Cells(J + K, "S").value)
Wend 'i think this
should work, it certainly works in my head!


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/