Conditional Formatting Questions

Hi

You wer close! Use the Formula Is option try this:
=AND(F3$F$2,F30)

Hope this helps.
--
Andy.


"MWE " wrote in message
...
I am running Excel2000 under WindowssXP. I wish to control the formats
for date cells in a given column that are than a given date. The
date to which other dates are to be compared is in cell F2.

This is easy to do in VBA and presently is one working approach.

But using Excel's builtin capability is normally more efficient and
requires no maintenance. Using conditional formatting (*Cell Value is*
method) , I can test a given cell against $F$2 and set the formats as I
want. The problem is that I do not want blank cells to be treated as
$F$2. I tried using two conditional formats: the first checking for
current = blank and the second checking for current $F$2. I assumed
that if the first test were true, the formatting would be applied and
_subsequent_tests_would_be_ignored._ But blank cells were treated as
$F$2 suggesting that all tests were executed in series, each "true"
overridding any above. So I reversed the order and first checked for
current $F$2 and then for current = blank. That did not work either,
i.e., blank cells were still treated as $F$2. Anyone have an
explanation for that??

Next I tried the *Formula is* method and something like
AND(test1,test2) where test1 tests for blank, and test2 tests for
$F$2, but that did not work. In fact, I discovered that no tests ever
"work" when using the Forumula Is method. For example, when using the
Formula Is method, I enter F3=$F$2 into the formula box and expect
that whenever the value in F3 is = to the value in F2, the test is true
and whatever I set for formatting options will occur. I then
copy/paste the value of F2 into F3 and the specified formatting does
NOT occur. So, what am I doing wrong?

Finally, I went back to Cell Value is method and used "Between" 1 and
$F$2-1 Setting the lower limit to 1 for a date is equivalent to
blank and setting the upper limit to $F$2-1 is equivalent to less than.
This worked, but I still want to understand why the other methods did
not.

Thanks


---
Message posted from http://www.ExcelForum.com/

Try a formula of

=AND(F3"",F3$F$2)

Note that the formula is preceded by =. Did you omit that?

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"MWE " wrote in message
...
I am running Excel2000 under WindowssXP. I wish to control the formats
for date cells in a given column that are than a given date. The
date to which other dates are to be compared is in cell F2.

This is easy to do in VBA and presently is one working approach.

But using Excel's builtin capability is normally more efficient and
requires no maintenance. Using conditional formatting (*Cell Value is*
method) , I can test a given cell against $F$2 and set the formats as I
want. The problem is that I do not want blank cells to be treated as
$F$2. I tried using two conditional formats: the first checking for
current = blank and the second checking for current $F$2. I assumed
that if the first test were true, the formatting would be applied and
_subsequent_tests_would_be_ignored._ But blank cells were treated as
$F$2 suggesting that all tests were executed in series, each "true"
overridding any above. So I reversed the order and first checked for
current $F$2 and then for current = blank. That did not work either,
i.e., blank cells were still treated as $F$2. Anyone have an
explanation for that??

Next I tried the *Formula is* method and something like
AND(test1,test2) where test1 tests for blank, and test2 tests for
$F$2, but that did not work. In fact, I discovered that no tests ever
"work" when using the Forumula Is method. For example, when using the
Formula Is method, I enter F3=$F$2 into the formula box and expect
that whenever the value in F3 is = to the value in F2, the test is true
and whatever I set for formatting options will occur. I then
copy/paste the value of F2 into F3 and the specified formatting does
NOT occur. So, what am I doing wrong?

Finally, I went back to Cell Value is method and used "Between" 1 and
$F$2-1 Setting the lower limit to 1 for a date is equivalent to
blank and setting the upper limit to $F$2-1 is equivalent to less than.
This worked, but I still want to understand why the other methods did
not.

Thanks


---
Message posted from http://www.ExcelForum.com/