M
MWE
I am running Excel2000 under WindowssXP. I wish to control the format
for date cells in a given column that are < than a given date. Th
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 an
requires no maintenance. Using conditional formatting (*Cell Value is
method) , I can test a given cell against $F$2 and set the formats as
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 fo
current = blank and the second checking for current < $F$2. I assume
that if the first test were true, the formatting would be applied an
_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 fo
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 a
explanation for that??
Next I tried the *Formula is* method and something lik
AND(test1,test2) where test1 tests for <> blank, and test2 tests fo
<$F$2, but that did not work. In fact, I discovered that no tests eve
“work” when using the Forumula Is method. For example, when using th
Formula Is method, I enter F3=$F$2 into the formula box and expec
that whenever the value in F3 is = to the value in F2, the test is tru
and whatever I set for formatting options will occur. I the
copy/paste the value of F2 into F3 and the specified formatting doe
NOT occur. So, what am I doing wrong?
Finally, I went back to Cell Value is method and used “Between” 1 an
$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 di
not.
Thank
for date cells in a given column that are < than a given date. Th
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 an
requires no maintenance. Using conditional formatting (*Cell Value is
method) , I can test a given cell against $F$2 and set the formats as
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 fo
current = blank and the second checking for current < $F$2. I assume
that if the first test were true, the formatting would be applied an
_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 fo
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 a
explanation for that??
Next I tried the *Formula is* method and something lik
AND(test1,test2) where test1 tests for <> blank, and test2 tests fo
<$F$2, but that did not work. In fact, I discovered that no tests eve
“work” when using the Forumula Is method. For example, when using th
Formula Is method, I enter F3=$F$2 into the formula box and expec
that whenever the value in F3 is = to the value in F2, the test is tru
and whatever I set for formatting options will occur. I the
copy/paste the value of F2 into F3 and the specified formatting doe
NOT occur. So, what am I doing wrong?
Finally, I went back to Cell Value is method and used “Between” 1 an
$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 di
not.
Thank