I was comparing two worksheets by creating formulas off to the side of one sheet. These returned True if the corresponding cells in the two sheets matched, False if they didn’t. I converted the Trues and Falses to values, and replaced the Trues with nothing. Then I threw some conditional formatting on the data in one of the sheets. The formatting referred to the area with empty cells and Falses. The condition was for cells offset from Falses to be shaded.
I expected a smattering of color. What I got was a solid swath of light orange. Kind of like this, but on three separate sheets:
I assumed that I’d screwed up the conditional formatting formula, a not unreasonable premise. However, after a couple of minutes I was sure I hadn’t, and it was then I realized that empty cells equate to False. I’m sure everybody else already knows this, so I figured I’d better dig a little deeper in case it comes up at a party or something.
Next I checked if cells with =”” equate to False. They don’t. Neither do ones with 0, which I thought they might, since False multiplied by 1 equals 0. Here’s the visual summary, this time with a lambent blue for False.
In VBA the results are similar, except that 0 = vbEmpty. I have no idea what that means. I’m pretty sure Dick had a post on this, called “testing for empty cells,” but it seems to have gone down in the great DDOE server crash of 2011.