Thanks, David. That got formatting going in the correct cell.
One more refinement - crossover dates. My column of date values has specific
dates, as you've seen. I want to construct a condition that picks the cell in
the next row down if today's date is greater than the one the condition is
operating on. Like this, table repeated for ease:
Row........Col B............Col F
No.....Month Ending.... Value
----....--------------....---------
32.....08/24/2008.....45,000
33.....09/24/2008.....45,250
34.....10/24/2008.....45,500
For today's date (09/21/2008), your logical AND formula will correctly pick
cell B33.
For the date 09/26/2008, two days beyond the date in cell B33, it will again
correctly pick the same cell since that day's month and cell B33's month are
the same and highllight cell F33. However, for this case, since the dates in
column B are "Month Ending", I want the condition formula to pick cell B34
and then highlight the value in cell F34, $45,500.
Thanks for your patience.
- Al Avery
"David Biddulph" wrote:
If you are wanting the same month, rather than the same date, try
=AND(MONTH($B6)=MONTH(TODAY()),YEAR($B6)=YEAR(TODA Y()))
or
=TEXT($B6,"yyyymm")=TEXT(TODAY(),"yyyymm")
--
David Biddulph
"Al Avery" wrote in message
...
Sorry, I neglected to say that I had tried that suggestion, too. After
I've
entered the condition and specified the formatting, the rule looks like
this:
Formula: =$B6=Today() Formatting: <yellow highlight Applies to:
=$F$6:$F$66
Still produces no highlighting.
If the following explanation correctly describes how this CF rule works,
then I'm not surprised that there's no highlighting:
Theoretical: The CF formula searches $B6:$B66 for the value TODAY(). When
it
locates that value, the condition is TRUE, as you said David. It then
highlights the cell in $F$6:$F$66 whose row is the same as condition
found.
Practice: If this is accurate, the condition will never return a TRUE
value
since the values in column B are discrete dates that were generated by
copying the top cell (B6) down to B66 using the lower-right-corner cross
and
choosing months from the smart tag.
Is this correct thinking?
Thanks,
- Al Avery
"David Biddulph" wrote:
In the "Formula is..." option in CF, you need a formula that returns a
TRUE
or FALSE condition, so that a TRUE gives your conditional formatting and
a
FALSE doesn't.
You had a condition =$B6=TODAY()
Why not try that in your CF "Formula is...", as Peo suggested?
--
David Biddulph
"Al Avery" wrote in message
...
I thought that "Equal to..." might be the problems as well. So I tried
the
"Formula is..." option. In the formula box I entered
"=vlookup(today()+30,B6:B66)". This formula points to the correct cell
and,
therefore, produces the correct result when used in a cell by itself,
i.e.,
09/24/2008. But this procedure also changed no formatting in the range.
Stymied.
- Al AVery
"David Biddulph" wrote:
I don't have Excel 2007, but the problem is that with the "Equal
to..."
option you have a used a rule equivalent to Excel 2003's "Cell Value
Is",
whereas you needed "Formula Is".
I think the Excel 2007 option is labelled "Use a formula to determine
which
cells to format".
--
David Biddulph
"Al Avery" wrote in message
...
Hum, didn't work for me. Using Excel 2007 and the example above, I:
Selected F6:F66
On the Home tab, selected Condition Formatting Highlight Cell
Rules
Equal to...
In the Equal to window, entered =$B6=TODAY() in the "Format cells
that
are
EQUAL to" box
Chose formatting
Clicked Ok
No cell formatting in the range F6:F66 changed.
Even when I entered a specific cell in the date range,=$B$33 (in my
example), no formatting changed
Perhaps I'm doing something incorrectly.
- Al Avery
"Peo Sjoblom" wrote:
Sorry, didn't notice that the dates are in B
Select F6:F66, but use formula is
=$B6=TODAY()
--
Regards,
Peo Sjoblom
"Peo Sjoblom" wrote in message
...
Select B6:F66, with B6 as the active cell do formatconditional
formatting
select cell value is equal to
and put
=TODAY()
in the box, select the formatting you want and click OK twice
--
Regards,
Peo Sjoblom
"Al Avery" <Al wrote in message
...
My worksheet has a number of columns. The second column is a
sequence
of
month-ending dates that are one month apart. The 5th column has
a
series
of
values. I want the 5th column's cell highlighted whose date in
the
month-ending column includes to today's date.
Here's an example (hope it comes out clealy enough with
proportional
font):
Row Col B ... Col F
No. Month Ending ... Value
---- -------------- ---------
32 08/24/2008 45,000
33 09/24/2008 45,250
34 10/24/2008 45,500
Since today's date is 09/20/2008, cell F33 should be
highlighted.
The actual array is B6:F66, without headers.
Many thanks for any help,