General Question

gunther's avatar

How can I make this conditional formatting rule in Excel?

Asked by gunther (169points) December 4th, 2014

I want to make a formatting rule in Excel that will change the color of the text in a cell only if it is different than the text in a cell of a different column in the same row. So basically, if A1 and B1 are the same, I want them to be the same color; but if they’re different, I want Excel to automatically change the color of B1.

Here’s an example of what I want it to look like.

I’m new to Excel and so far I’ve been able to find everything through Google and Youtube, but I haven’t been able to figure this one out. It’s probably simple, but I can’t seem to find a good explanation online.

Observing members: 0 Composing members: 0

4 Answers

CWOTUS's avatar

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.

gunther's avatar

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.

CWOTUS's avatar

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.

Response moderated (Spam)

Answer this question




to answer.

This question is in the General Section. Responses must be helpful and on-topic.

Your answer will be saved while you login or join.

Have a question? Ask Fluther!

What do you know more about?
Knowledge Networking @ Fluther