Excel help!

[Absol197]

Hey Playgrounders, I tried searching the interwebs for assistance, but nothing seems to be quite what I'm looking for, so I decided to ask you!

I've got a project on Excel 2007, and I need some help. Specifically, conditional formatting. Let's say I have a column, and let's say I want the numbers in that colum to be formatted as green when they match with the number in the same row in a different column. Otherwise, I want them to be red.

Now, I know that this would be two rules (one to make them green if "yes," one to make them red if "no"), but how to set them up is not working for me at all. So, how would I create these rules?

Thanks in advance to all for your help!


~Phoenix~

[Brother Oni]

I don't have a copy of Excel on my home computer, but it sounds like only one rule to me.

Format all cells in the column to have red text.
Insert formatting rule that changes text colour to green if cell value matches the target cell.

The issue is, you need a different rule for each cell otherwise they will all check the same cell, which is why it's not working. Depending on the length of the data entry columns, this may not be feasible.

Off the top of my head, the best kludgy workaround I can think of is to have two additional columns which check whether the values are identical:

Column 1: =IF(a1=b1,a1,"")
Column 2: =IF(a1=b1,"",a1)

Where column A is the first column and B is the reference column, then text colour format Column 1 to green and Column 2 to red. This way, you'll get the data cell repeated in the desired colour.

When I'm back at work, I'll see if I can come up with something more elegant.

[Manga Shoggoth]

I used conditional formatting briefly. It turned out to be more trouble than it was worth, especially when copy/paste operations were involved.


You may find it easier to write a VBA function to change the cell colours, and have it operate across the entire worksheet. You can then trigger it manually or - if we are dealing with a small number of fields or ranges - use it in a blank cell passing in the ranges in question as parameters.

EDIT: If you aren't in a hurry I might be able to experiment at the weekend, but it's my last day in work tomorrow, and I expect to be rather busy...

[Jack Squat]

Which Excel are you using? I've got 2010 on my computer, so I'm going to be using that to explain.

The easiest way to do it is to just have the normal text red, and then put in a conditional formatting to change it when it matches.

So let's say that I wanted cell C8 to change when it matched cell D8.
I would change the color in C8 to be red.
Then I would select the Conditional Formatting button from the Home tab and select Highlight Cell Rules -> Equal to.

On the window that pops up, it says "Format cells that are EQUAL TO:" and on the window I'd type in '=$D$8' and then select "Custom format..." from the drop down menu and change the color to green.


If you want to have it so that you specify that it changes the color when the values are not equal, create the above rule, and then go to Conditional Formatting -> New Rule -> Format only cells that contain

and then where it says "Format only cells with"

[Cell Value] [Not Equal To] [=$D$8]

Then click 'Format...' and change the color to red.

But really, that's extra work that you don't need to do.

[Brother Oni]

After playing around in Excel 2010, you can get an automatically updating conditional formatting rule by using Jack Squat's method, but plugging in '=$D8' instead of '=$D$8', then just extending the series down ($D$8 will make all cells in column C to compare to D8 and D8 only).

Unfortunately, I'm not sure this method works in Excel 2007.

[Absol197]

Thanks everybody! I'll try some of these tricks today, and see if they work. I'll report back!

Thanks again!


~Phoenix~