+ Reply to Thread
Results 1 to 7 of 7

Copy A Conditional Formatting Rule In Multiple Cells In One Column

  1. #1
    Registered User
    Join Date
    10-17-2014
    Location
    Folsom, California
    MS-Off Ver
    2010
    Posts
    10

    Copy A Conditional Formatting Rule In Multiple Cells In One Column

    I enter a date in column A, and then use a simple equation in column B to add 30 days to it. Then in column C, I will enter another date and this is where the conditional formatting comes in. If the date in Column C is less or equal to the date in Column B, I want the cell in Column C to turn green. If the date in Column C is greater than the date in Column B, I want the cell to turn red. The conditional formatting I set up works fine for row 2.

    =$G$2=$B$2 green
    =$G$2<$B$2 green
    =$G$2>$B$2 red

    Now, what I want to do is copy the formatting down the column so that it is row specific. What is happening now is that no matter what row I am in, the conditional formatting equation references row 2. I've tried copying down in a number of ways, but the formatting equation does not correct for the row. Is there any way I can make this work?

  2. #2
    Forum Expert gmr4evr1's Avatar
    Join Date
    11-24-2014
    Location
    Texas
    MS-Off Ver
    Office 2010 and 2007
    Posts
    3,448

    Re: Copy A Conditional Formatting Rule In Multiple Cells In One Column

    Remove the $ signs from your formula and copy down
    1N73LL1G3NC3 15 7H3 4B1L17Y 70 4D4P7 70 CH4NG3 - 573PH3N H4WK1NG
    You don't have to add Rep if I have helped you out (but it would be nice), but please mark the thread as SOLVED if your issue is resolved.

    Tom

  3. #3
    Forum Expert skywriter's Avatar
    Join Date
    06-09-2014
    Location
    USA
    MS-Off Ver
    2016
    Posts
    2,760

    Re: Copy A Conditional Formatting Rule In Multiple Cells In One Column

    Select all the cells you want the conditional formatting to apply to, then go into the formula and remove the dollar signs before letter and number. Excel will copy the formula internally. The dollar signs are making the formula refer to the same cells. If you put that into a cell and copied it down you would see it never changes.
    Click the * Add Reputation button in the lower left hand corner of this post to say thanks.

    Don't forget to mark this thread SOLVED by going to the "Thread Tools" drop down list above your first post and choosing solved.

  4. #4
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: Copy A Conditional Formatting Rule In Multiple Cells In One Column

    You need to make the row references relative (remove the $'s from the Row#s)

    =$G2=$B2 green
    =$G2<$B2 green
    =$G2>$B2 red

    Also, instead of copy pasting the the formatting down the column..
    while you're in the conditional formatting rules manager window,
    You can just change the 'Applies To' Range to reflect the appropriate range, like =$C$2:$C$1000

  5. #5
    Registered User
    Join Date
    10-17-2014
    Location
    Folsom, California
    MS-Off Ver
    2010
    Posts
    10

    Re: Copy A Conditional Formatting Rule In Multiple Cells In One Column

    Thanks to all of you. Your suggestion worked perfectly.

  6. #6
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: Copy A Conditional Formatting Rule In Multiple Cells In One Column

    You're welcome.

  7. #7
    Forum Expert gmr4evr1's Avatar
    Join Date
    11-24-2014
    Location
    Texas
    MS-Off Ver
    Office 2010 and 2007
    Posts
    3,448

    Re: Copy A Conditional Formatting Rule In Multiple Cells In One Column

    No problem

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Multiple conditions in One rule of Conditional Formatting
    By tanmayjajoo in forum Excel General
    Replies: 10
    Last Post: 08-13-2014, 12:53 PM
  2. Replies: 11
    Last Post: 05-09-2013, 04:25 AM
  3. [SOLVED] Multiple Rule Conditional Formatting
    By jbenkov in forum Excel General
    Replies: 8
    Last Post: 11-30-2012, 12:21 PM
  4. Replies: 2
    Last Post: 09-18-2009, 07:07 PM
  5. Multiple Criteria per conditional formatting rule
    By Jonathan78 in forum Excel General
    Replies: 13
    Last Post: 09-18-2009, 05:50 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1