Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I have a list of vaules in column A and column B. I need to highlight where there is a difference between values that are in the same row.

I've used a simpe formula in B1: $A1<>$B1 (format bold). It works (values are different), but now I would like to copy and paste special this format to all the values in column B, so that it refers to it's neighbour in column A.

I.e. A1<>B1; A2<>B2, etc down to row 100.

Problem is the formula keeps using the original reference. Therefore everything is bold. Surely I dont need to copy and paste the CF to each individual value in column B (please no!)

I know I'm missing a step. Please let me know what it is.

share|improve this question
    
Does the cell have bold formatting applied? You may be copying a bold format, as well as conditional formatting, across cells. –  Chris Kent Jun 6 '11 at 15:11
    
No bold formatting. I've changed to red fill. Only problem is it still doesn't work. I've tried suggestions below, but it seems to be a problem of my 'Applies to'f field reverting to absolute references each time. No matter how many times I remove $ signs it puts them back in. –  RocketGoal Jun 10 '11 at 13:14
add comment

4 Answers

up vote 0 down vote accepted

I don't believe this is a problem with cell references as one of the previous answers implied. When you create Conditional Formatting rules in Excel 2007 or higher, you have to specify the range it applies to.

  1. Conditional Formatting >> Manage Rules >> Show Formatting Rules for >> Set to This Worksheet

  2. Double-click the rule you want to edit. Hit New Rule if you want to start over.

  3. Select Format only cells that contain > Set to Cell Value | Not equal to | =$A1

  4. Set your preferred format and click OK.

  5. On the Conditional Formatting Rules Manager, set the Applies to field to:

    =$B$1:$B$100

enter image description here

Click OK/Apply. You don't have to copy paste any formats.

share|improve this answer
    
This worked. Thanks. –  RocketGoal Jul 5 '11 at 8:34
    
I should also add that your explination was clear and very useful Thanks again for your time in helping me. Much appreciated. –  RocketGoal Jul 5 '11 at 9:44
add comment

Just go to CF->Manage Rules, and then change the "Applies to" field to B1:B100. Also, your formula should be

=$A1

if you are defining the formatting for column B.

share|improve this answer
    
Tried but it's highlighting all column B (red). Most of the column B figures are the same as their colum A figure in the same row, so I wouldn't expect it to highlight those. Unfortunately my "applies to" field is always including ABSOLUTE references. Can't seem to amke it a simple B1:B100. Ideas? –  RocketGoal Jun 10 '11 at 12:50
    
I can't see any pictures unfortunately - works firewall has decided they are ot 'suitable'. Great! –  RocketGoal Jun 10 '11 at 12:51
add comment

The '$' sign is for absolute references where no dollar sign is for a relative reference. What you want is a relative reference meaning if the conditional format for cell A1 has a relative reference to cell B1 when you copy the CF, the relative reference stays in the same spot relative to the cell.

Original A1 --> B1
New A2 --> B2

Original A1 --> $B$1
New A2 --> $B$1

I've often found that in particular a conditional format doesn't copy well to other cells using the above picture. The solution I have used to extend the conditional format is the formula If(A1<>B1, TRUE, FALSE) and use an expression. Here is the box for Excel 2010.

enter image description here

share|improve this answer
2  
This is true, but in this case the OP was using $A1 and $B1 only makes the column identifier absolute, so that shouldn't obstruct extending the conditional formatting down a column, because the row number isn't fixed. –  DMA57361 Jun 7 '11 at 7:55
    
I agree with DMA57361 that the OP's question doesn't involve problems with absolute references. The CF rule you proposed is viable, although the formula in your screenshot could be shortened to "=A2<>B2" (without the quotes). –  Ellesa Jun 8 '11 at 10:33
    
Even though I change the refernce type ($, or $$) and click apply/ok, then copy the cell and paste special/format (red fill)only down the relevant column it doesn't work. The whole column goes red. which isn't correct. When I look at the CF on a specific selection it is using absolute references for my 'Applies to', and refers to my original =$A!<>$B1 refernce. Even though my selection is B15. Stuck. Any help appreciated. –  RocketGoal Jun 10 '11 at 13:06
    
Have you tried using Paste Special --> Formats. The technique I demo'ed above worked for me. –  wbeard52 Jun 11 '11 at 20:19
    
Paste Special > formats is what I've been using, so I'm stuck for what else to do. –  RocketGoal Jun 14 '11 at 13:56
show 1 more comment

For what you're trying to do, you probably just want to set B1 to have bold formatting if it doesn't equal A1 using the explicit "this cell" formatting, like so:

Excel 2003 Conditional Formatting window, showing - cell value is: not equal to: A1

Note, this is a shot from Excel 2003, but you can get to the same inputs in 2007 and newer (I think "more rules" then "only cells that contain..."), I just don't have a that version on this machine to take an image.

From here you can copy the cell and paste special, formats to the entire column.

share|improve this answer
    
The left most combo-box can be changed to Formula Is. You can then add any formula you like and you don't have to reference the formatted cell. –  Chris Kent Jun 6 '11 at 15:06
2  
Indeed @Phydaux, but why do =A1<>B1 when you're using cell B1 already? By doing it this way your condition is explicitly comparing "this cell" to another, and is (in my opinion) less error prone. I'd suspect the OP has probably ended up with ="A1<>B1" in the formula box by mistake, or something of that nature. I've also now edited my answer, as on a second read I can see that first bit was a bit daft... –  DMA57361 Jun 6 '11 at 15:10
    
well, if you are comparing a value against the formatted cell then the way you have described is the best way. It's clearer and less error prone, I agree. I was just answering your question. But now you've edited it, my comment makes me look a little mad :P –  Chris Kent Jun 6 '11 at 15:15
    
@Phydaux that's OK, the first line of the answer was a little "off" anyway, so now we both just look a little mad. ;) –  DMA57361 Jun 6 '11 at 15:16
1  
Agree with @DMA57361 - if the formula is surrounded by ".." then copying it will copy it as-is and the cell references will not be updated. Excel might have inserted the ".." if the formula was initially entered without the preceeding =. –  w3d Jun 6 '11 at 15:18
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.