1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Conditional Formatting with Formula in Excel 2007

Discussion in 'Business Applications' started by jessiew84, Aug 4, 2010.

Thread Status:
Not open for further replies.
Advertisement
  1. jessiew84

    jessiew84 Thread Starter

    Joined:
    Jun 24, 2010
    Messages:
    23
    Good afternoon,

    First and foremost, I have over 3000 cells that I need to apply this conditional format to, so if possible I'd like to avoid having to manually update each formula.

    Bascially I have a conditional format set up so when the value in cell A="no" or A="n" then all the cell rows that correspond will automatically turn red. I've managed to get this far with no issues. However, I now have to copy this conditional format to the remaining 3000 cells below. I've found that simply copying and pasting the cells does not work becaue the formula is not auto updating so all the cells below are still referencing the inital cell A in my formula.

    My Question: Is there a way to copy a conditional format and apply it to cells and have it auto update the referenced cell?

    Here is a screen shot to better illustrate my dilema.

    Here what my workbook is currently looking like when I simply copy the cells and paste them into cells below. The conditional format formula is still referencing cell A3 and reads as =OR($A$3="no",$A$3="n") for the second section even though it should reference =OR($A$16="no",$A$16="n").
    [​IMG]
     
  2. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,608
    First Name:
    Hans
    Edit the first cell containing the conditional formatting, remove the $ signs since these are interpreted as fixed reference.

    It's the same as with a regular formula; let's take the following example:

    B2 = $A$2
    and you copy thsi formula all the way down the B column, every Bx row = $A$2

    however is you entre B2 = A2 and copy then Bx = Ax
    The first $sign fixes the column, the $ sign before the rownumber fixes the row N.

    Back to the coditional formatting: after you edit the formatting for the cell or cells, make sure there are NO $ signs
    then select that cel click Copy

    Select all the cells that need that conditional formatting an right-click Paste Special and choose Format
     
  3. jessiew84

    jessiew84 Thread Starter

    Joined:
    Jun 24, 2010
    Messages:
    23
    I already tried that and it didn't do what I want it to do. When I change the formula by removing all $ it will only conditional format the first cell. See below.

    [​IMG]

    Here is the setup that I did. When I remove the $ from the conditional format manager it will put them back in. However I was able to remove them from the actual formula and that stayed, but gave me the result above.
    [​IMG]
    [​IMG]
     
  4. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,608
    First Name:
    Hans
    As far as I can make up from the screenshots the A column consists of merged ranges, In that case you will have to include the $A$3 for the first range
     
  5. jessiew84

    jessiew84 Thread Starter

    Joined:
    Jun 24, 2010
    Messages:
    23
    Not all of column A is one giant merged cell as you can see in the initial post screen shot. Only a grouping of cells are merged.

    When the merged cell A3:A14 has either "n" or "no", I want the range B3:X14 to be red.

    When the merged cell A16:A27 has either "n" or "no", I want the range B16:X27 to be red.

    Etc..

    By simply coping and pasting the format through Paste Special > Formats...it will copy the format but B16:X27 conditional formatting will have the formula still referencing A3:A14. So if there's a "n" or "no" in A3:A14 it will change the format to all red for cells B3:X14 AND B16:X27.
     
  6. jessiew84

    jessiew84 Thread Starter

    Joined:
    Jun 24, 2010
    Messages:
    23
    AND, even if I unmerge cells in column A, and then remove the $ from the formula to read as =or(A3="no",A3="n")...this still will only highlight the first cell B3 in the whole group of cells the conditional format is supposed to be applied to on the conditional format manager which indicate it SHOULD be applied to cells $B$3:$X$14.
     
  7. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,608
    First Name:
    Hans
    If you can post the sample you're working on maybe we can see what really is the matter, there must be something else there
     
  8. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/940732

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice