How can I make this conditional formatting rule in Excel?

Conditional formatting isn’t too difficult, but the interface is unusual (not like other Excel interfaces), so that takes some getting used to.

Assuming you know how to access the commands via the menu, I’ll start with what you need to do while you’re there.

Since the column whose color needs to vary is B:B, go to a cell in your affected range in Column B:B and call up the Conditional Formatting dialog. You’ve chosen B1, so let’s start in that cell.

Select “Create a new rule” and “Based on a formula”. (Keep in mind that I don’t have Excel open right now, so I’m sort of winging this from memory – I do this a lot, but I don’t necessarily recall the exact wording of the menus and dialogs.)

When the dialog for the conditional formatting comes up, then enter a formula to be evaluated such as “B1 <> A1” (without the quotation marks). The formula you have entered is “B1 does not equal A1”. You could make the conditional less than, greater than, less than or equal to, etc. But you chose simple inequality, and that’s okay. (You could set up one color for “less than” and another for “greater than” if you wanted to, but let’s stick with the baby steps for now.) Be careful while editing formulas for evaluation. I think that if you use the left or right arrows, then Excel will attempt to modify the range of the formula that you’re editing. That’s one of the funky things about these dialog boxes.

So you’ve chosen an evaluation to make, and the next step is to set the format of your “condition when true”. Modify the cell’s color, font, border, etc. as you would like it to appear when “this condition is true”.

Next set the range that the condition should apply to. It may be all of B:B or only a few cells or only a single cell, for you to decide. You can type in the range or select it with the mouse.

You should be all set, and able to test your conditional formatting setup. (You can set up to three conditional formats per cell, I believe, through the menu. If you want to do more conditions than that, then you’ll need to do it with macros. Keep in mind that too many conditional formats make the spreadsheet more difficult for most users to comprehend, so definitely try to keep it simple.)

When you’re ready for more advanced formatting, such as comparing all of the values in an entire range to a single cell, then use relative cell referencing as applicable, such as “B1 <> $A$1”, for example, applied to a number of cells in B:B.

You’ll get it if you just keep playing with it.

Thanks for your reply! I made the rule, but when it comes to setting the range, I’m not quite sure what to do. Here’s where I’m at now. In the “Applies to” box I tried to put B:B and a few other things, but none of the things I tried worked.

You may have to edit the formula in the formula box to take out the double-quotes (”) where they occur. That may be leading to an improper evaluation of the formula. (In simple terms, the equality checker you show is looking for a formula with the literal contents of the cell equal the literal string “B1 <> A1” instead of making the comparison between B1 and A1 and evaluating based on that comparison.)

In the “applies to” box, there should not be an equals sign, only the range of applicability, such as “B:B” (again, leave out the double-quotes), or “B1:B100”. No equals sign, since there’s no equation to evaluate.