can anyone help with a formula for conditional formatting? here's the
scenario. I have a sheet that i'm using to track dues paid by members over a
period of months.
Row 1 is dedicated to headings
Col A is dedicated to member names.
Cols B:R i have (in row 1) months in the calendar year, JAN 09, FEB 09, Etc.

subsequent rows for each member show the number of dollars that they have
paid in each month totalling in col S.

Each positive nonzero entry auto formats to green fill, each entry with a -1
in it formats to gray (before they joined). The heading cell for the current
month auto highlights to blue.

Here is what i need. I want to format to red all cells which have the 2
true conditions. The Cell is blank (easy part). The cell is in the column
or to the left of (earlier in the year than) the current month's column. Can
anyone help?

Any formula you can construct into a CF can be placed right into a formula as
well. One of your CF formulas is =B2=-1, or something like that, right?

You can add that into a COUNTIF like so to get the number of cells that are
currently TRUE for that formula:

=COUNTIF(B2:R2,-1)

Do the same thing for your other formula and then add the two COUNTIF
formulas together.

=COUNTIF(B2:R2,-1)+=COUNTIF(B2:R2,"0")

Hmm, now that I look at that...aren't you just saying "count the number of
cells that aren't blank or don't have a value = to zero? If so, one COUNTIF
does that:

=COUNTIF(B2:R2,"<0")
--
"Actually, I *am* a rocket scientist." -- JB

Your feedback is appreciated, click YES if this post helped you.


"Karl" wrote:

can anyone help with a formula for conditional formatting? here's the
scenario. I have a sheet that i'm using to track dues paid by members over a
period of months.
Row 1 is dedicated to headings
Col A is dedicated to member names.
Cols B:R i have (in row 1) months in the calendar year, JAN 09, FEB 09, Etc.

subsequent rows for each member show the number of dollars that they have
paid in each month totalling in col S.

Each positive nonzero entry auto formats to green fill, each entry with a -1
in it formats to gray (before they joined). The heading cell for the current
month auto highlights to blue.

Here is what i need. I want to format to red all cells which have the 2
true conditions. The Cell is blank (easy part). The cell is in the column
or to the left of (earlier in the year than) the current month's column. Can
anyone help?

Not quite what i was looking for, I need it to discern whether the cell is in
a column with either the current month at the header row or a previous month
so I can red flag who has not paid dues up to the current month.

"Karl" wrote:

can anyone help with a formula for conditional formatting? here's the
scenario. I have a sheet that i'm using to track dues paid by members over a
period of months.
Row 1 is dedicated to headings
Col A is dedicated to member names.
Cols B:R i have (in row 1) months in the calendar year, JAN 09, FEB 09, Etc.

subsequent rows for each member show the number of dollars that they have
paid in each month totalling in col S.

Each positive nonzero entry auto formats to green fill, each entry with a -1
in it formats to gray (before they joined). The heading cell for the current
month auto highlights to blue.

Here is what i need. I want to format to red all cells which have the 2
true conditions. The Cell is blank (easy part). The cell is in the column
or to the left of (earlier in the year than) the current month's column. Can
anyone help?