Closed Thread
Results 1 to 7 of 7

How to apply conditional formatting rule to all rows in spreadsheet

  1. #1
    Registered User
    Join Date
    11-20-2008
    Location
    Melbourne, Australia
    MS-Off Ver
    Office 2007 Professional
    Posts
    20

    How to apply conditional formatting rule to all rows in spreadsheet

    I have to deal with a spreadsheet in Excel 2007 at the moment where I have a range of about 650 rows (order numbers) with about 5 columns which show a comparison of cost for different orders. I want to figure out which of the prices is the lowest one for each order and copy that to all cells to see it for each row.

    What I already did is, to the format some rows with this conditional formatting rule:

    =$D$45:$H$45 in Bottom 1 of the Rules Manager.

    I know how to use the Format Painter, but because I have to compare the prices for each row it would take too much time to copy the formatting only to each single row.

    Is there an easier way than using the format painter to individually copy the conditional formatting rule to each row?

    Any help would be appreciated!

    Thanks in Advance!

  2. #2
    Forum Expert Palmetto's Avatar
    Join Date
    04-04-2007
    Location
    South Eastern, USA
    MS-Off Ver
    XP, 2007, 2010
    Posts
    3,978

    Re: How to apply conditional formatting rule to all rows in spreadsheet

    Select the first data cell on the first row, the press Ctrl+A, which will select the current region of cells.

    Go to Conditional Formatting and remove any previous format, the re-apply the CF using your formula with absolute reference to the target cell you are watching.

    BTW, please update your profile to indicate which version of Excel you are using. Some answers are version specific, depending on the question

  3. #3
    Registered User
    Join Date
    11-20-2008
    Location
    Melbourne, Australia
    MS-Off Ver
    Office 2007 Professional
    Posts
    20

    Re: How to apply conditional formatting rule to all rows in spreadsheet

    I have tried what you have explained however it did not work.

    I want to apply the conditional formatting rule that I have created for $D$2:$H$2 to every single row. For example:

    $D$3:$H$3
    $D$4:$H$4
    $D$5:$H$5
    $D$6:$H$6
    ....
    $D$643:$H$643

    The only way that I have found to do this is manually use the format painter and copy the conditional formatting rule in $D$2:$H$2 to every single row down to $D$643:$H$643.

    Is there an easier way to do this?

  4. #4
    Forum Expert Palmetto's Avatar
    Join Date
    04-04-2007
    Location
    South Eastern, USA
    MS-Off Ver
    XP, 2007, 2010
    Posts
    3,978

    Re: How to apply conditional formatting rule to all rows in spreadsheet

    I have tried what you have explained however it did not work.
    The directions I gave work when followed.

    I want to apply the conditional formatting rule that I have created for $D$2:$H$2 to every single row
    What is your formula/rule for Conditional Formatting?

    Is there an easier way to do this?
    Yes. First - clear all conditional formatting from the target range

    For discussion, say you want to CF the range D3:H6
    1. Starting in cell D3, highlight the entire range of cells from D3:H6
    2. Choose Conditional Formatting on the Ribbon
    3. Chose New rule, Formula Option
    4. Enter the formula, click OK. Verify that the "Applies To" field shows the range D3:H6. If so click OK. If not, Cancel and reselect the correct range and follow the above steps to this point.

    See the attached worksheet.
    Conditional Formatting was applied to the range A1:H10 all at once using the above process. Type the letter "a" into any cell in the range A1:A10 to see the CF being applied.
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    11-20-2008
    Location
    Melbourne, Australia
    MS-Off Ver
    Office 2007 Professional
    Posts
    20

    Re: How to apply conditional formatting rule to all rows in spreadsheet

    I want to use "Bottom Ten Items" to find out the cheapest price for every single row to see for each single order who is the cheapest carrier to optimize freight costs in the future.

    I've attached a short part of the list where the first row contains the conditional formating rule that I want to apply to the whole list for each single row.

    Can you help me again with that please?
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    02-18-2015
    Location
    USA
    MS-Off Ver
    2013
    Posts
    9

    Re: How to apply conditional formatting rule to all rows in spreadsheet

    Hello Lethal,

    I see this problem was never solved. It's been now about 6 year since you were faced with this problem and now I am in your shoes.

    I really need very badly to apply CF across rows not columns. Ideally, Conditional formating has to be done with icon sets.


    Pls see the picture below.

    Any idea how this can be done to a whole range vs. manually applying Conditional formating to each row. Please let me know.



    Capture.JPG



    Tks,
    Last edited by nikkollai; 02-18-2015 at 02:30 AM.

  7. #7
    Forum Moderator Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: How to apply conditional formatting rule to all rows in spreadsheet

    Welcome to the forum. Pls take some minutes to read forum rules, because:

    Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

    If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

    Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.
    Regards

    Fotis.

    -This is my Greek whisper to Europe.

    --Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.

    Advanced Excel Techniques: http://excelxor.com/

    --KISS(Keep it simple Stupid)

    --Bring them back.

    ---See about Acropolis of Athens.

    --Visit Greece.

Closed Thread

Thread Information

Users Browsing this Thread

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

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