+ Reply to Thread
Results 1 to 5 of 5

Excel 2007 : Conditional formatting: Format multiple rows based on same criteria

  1. #1
    Registered User
    Join Date
    12-12-2011
    Location
    Ljubljana, Slovenia
    MS-Off Ver
    Excel 2007
    Posts
    4

    Conditional formatting: Format multiple rows based on same criteria

    Hi,

    I am have a table with 4 columns and I need to color cells in each row from lowest to highest number. I've done that using conditional formating.

    The trickly part is that I have several hundred rows that I need to apply that rule to and I will have to do that quite often.. I know I can do it by using format painter and then paste rule to each row manually, but is there a faster way?.

    Thank you.


    M.
    Last edited by sempera; 12-17-2011 at 06:13 PM. Reason: found a suitable solution

  2. #2
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Conditional formatting: Format multiple rows based on same criteria

    Attach a sample of your file so we can understand your data better and offer you a quick solution.
    If I have helped, Don't forget to add to my reputation (click on the star below the post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
    Use code tags when posting your VBA code: [code] Your code here [/code]

  3. #3
    Registered User
    Join Date
    12-12-2011
    Location
    Ljubljana, Slovenia
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: Conditional formatting: Format multiple rows based on same criteria

    there is my data, i need to color just cells with numbers of course.
    Attached Files Attached Files

  4. #4
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Conditional formatting: Format multiple rows based on same criteria

    Assuming this is Excel 2007 you are working with... then go to E1, and go to Home tab|Conditional Formatting|Manage Rules. You just need to change the Applies To range to encompass the whole range, i.e. =$E$3:$H$41
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  5. #5
    Registered User
    Join Date
    12-12-2011
    Location
    Ljubljana, Slovenia
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: Conditional formatting: Format multiple rows based on same criteria

    Got it to work, its not totally automatic, but its still quite fast. The tricky part for me was that I had to compare each row individually and some parts of the data had more columns than the others.

    What I did at last was set multiple conditional rules to the first row I had to start with ( I Selected more columns in that row than the widest data range in my worksheet). I didnt want to lose my formatting.

    Rules were:
    1. For cells that are empty I chose a format without borders.
    2. For cells that were not empty I chose a format with borders that I had.
    3. I chose a coloured rule that I wanted.

    Then i pressed Format painter twice and just held down arrow till the end of the range.

    Then at the start of the next range I clicked on first row and down arrow again. Etc.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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