Toggle Cell Colour Conditionally

[Ken McLennan]

G'day there One & All,

I'm back again, and (surprise, surprise) I'm stumped and asking
for a hand.

I have a fairly simple worksheet containing hours worked in
columns with the last day of the pay fortnight in Column 1. There can be
any number up to about 12 or so, of entries for a particular date but
the dates are always grouped together before going on to the next
fortnight:

Date |
18/07/08 | Standard Hours | 80
18/07/08 | Overtime | 6
18/07/08 | On Call | 12
01/08/08 | Standard Hours | 8-0
01/08/08 | Overtime | 0
01/08/08 | On Call | 14

I want the conditional formatting to alternate with (say) a pale
green for all entries of the first date & white for the next, swapping
back when it changes again, etc, etc.

Oh, and at some time there may well be a late claim and the date
will be anything up to 6 months older so a < or > might not be
effective.

The background formatting is irrelevent but I'm trying to figure
out not only how to determine when the date changes, but how to get the
background colour to toggle back & forth. I fear I may be trying to do
something that's not possible (unless with VBA?)

Google has given me a few pointers, but nothing I could make work
properly (or at all).

If anyone has any ideas, I'd be only too happy to hear them.

Thanks for listening,
Ken McLennan
Qld, Australia

 

[T. Valko]

Try this...

Assume your dates are in the range A2:A10

Select the range A2:A10
Use this as the conditional formatting rule:

=MOD(ROUND(SUMPRODUCT((A$2:A2<>"")/COUNTIF(A$2:A2,A$2:A2)),0),2)

 

[Ken McLennan]

G'day there Biff,

Try this...

Assume your dates are in the range A2:A10

Select the range A2:A10
Use this as the conditional formatting rule:

=MOD(ROUND(SUMPRODUCT((A$2:A2<>"")/COUNTIF(A$2:A2,A$2:A2)),0),2)

Worked like a charm thanks mate. Much appreciated. I'll just have
to get it to work across the columns, but I'm sure I can figure it out
without too many ulcers

Thanks once again,

See ya
Ken McLennan
Qld, Australia

 

[T. Valko]

Just select the entire range and make the column references in the formula
absolute:

=MOD(ROUND(SUMPRODUCT(($A$2:$A2<>"")/COUNTIF($A$2:$A2,$A$2:$A2)),0),2)

 

[Ken McLennan]

G'day there Biff,

Just select the entire range and make the column references in the formula
absolute:

=MOD(ROUND(SUMPRODUCT(($A$2:$A2<>"")/COUNTIF($A$2:$A2,$A$2:$A2)),0),2)

Of course!! It's so obvious now that you've pointed it out. I
think I'd have figured it eventually as I've done it previously, but I
don't recall how. Probably as you've suggested here, but it was more by
good luck than good management

Thanks once again,
Ken McLennan
Qld, Australia

 

[Ken McLennan]

G'day there Biff,

Of course!! It's so obvious now that you've pointed it out. I
think I'd have figured it eventually as I've done it previously, but I
don't recall how. Probably as you've suggested here, but it was more by
good luck than good management

I rescind that. Having had a good look at the formula you've
devised, I'm no longer sure that I would have managed to get it to do
what I want. I'm quite happy to defer to your greater knowledge

Thanks very much for your help,
Ken McLennan
Qld, Australia

 

[T. Valko]

You're welcome. Thanks for the feedback!