1

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.

2
  • 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, 2011 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, 2011 at 13:14

4 Answers 4

0

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.

1
  • 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, 2011 at 9:44
1

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.

2
  • 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, 2011 at 12:50
  • I can't see any pictures unfortunately - works firewall has decided they are ot 'suitable'. Great!
    – RocketGoal
    Jun 10, 2011 at 12:51
1

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

6
  • 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, 2011 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, 2011 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, 2011 at 13:06
  • Have you tried using Paste Special --> Formats. The technique I demo'ed above worked for me.
    – wbeard52
    Jun 11, 2011 at 20:19
  • Paste Special > formats is what I've been using, so I'm stuck for what else to do.
    – RocketGoal
    Jun 14, 2011 at 13:56
0

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.

5
  • 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, 2011 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, 2011 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, 2011 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, 2011 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 =.
    – MrWhite
    Jun 6, 2011 at 15:18

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .