+ Reply to Thread
Results 1 to 9 of 9

Loop and apply conditional formats through a range row by row

  1. #1
    Forum Contributor BeachRock's Avatar
    Join Date
    11-01-2011
    Location
    Oregon, United States
    MS-Off Ver
    Excel 2016
    Posts
    401

    Question Loop and apply conditional formats through a range row by row

    I'm trying to find an easier way to apply two separate conditional formats to a range of cells within multiple rows. Going through and manually applying these settings for as many rows I have to do is going to take forever, otherwise.

    So, I recorded a macro for three of the row ranges and created both conditional formats for each row range and below is what I ended up with.

    Please Login or Register  to view this content.
    These are the first 3 rows of ranges. For this sheet, I need to apply these same CFs to rows 9 through 77. On the next sheet I have to do, the same CFs will need to be applied to 144 rows and the last sheet I have to do will have 34,560 rows that needs these CFs applied. As you can imagine, manually applying every CF individually to each row range will take an eternity to complete. So here I am, hoping you can help save me from weeks or months of tedious, mind numbing work.

    I thought that maybe there is a way to modify the code above to make it loop through a range of rows, each row having it's own range of cells within it to change, and apply the two conditional format rules.

    The rows in this case are rows 6 through 77. Each row uses the same basic range, such as B6,D6:J6 or B7,D7:J7. As you can see in the code above, there are two formulas for criteria pertaining to each CF for each row range. For row 6 the formulas are =$K$30>0 and $K$30<1. $K$30 will remain constant for every row range while the > and < numbers will increment by 1 each for every row range. So, row 7 formulas would be $K$30>1 and $K$30<2 and row 8 formulas would be $K$30>2 and $K$30<3, and so-on until completed through row 77 for this sheet.

    Let me know if you need any other information, and thanks for your help.
    Last edited by BeachRock; 10-10-2012 at 10:51 AM. Reason: Add more information to original for better outcome description
    -------------
    Tony

  2. #2
    Valued Forum Contributor
    Join Date
    08-14-2012
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    573

    Re: Loop and apply conditional formats through a range row by row

    Hi, For 77th row in the code, you used the reference of K30 in the conditional formatting. So will 78th row refers to K31? Then you can apply it directly in the application, instead of using macro.

  3. #3
    Forum Contributor BeachRock's Avatar
    Join Date
    11-01-2011
    Location
    Oregon, United States
    MS-Off Ver
    Excel 2016
    Posts
    401

    Re: Loop and apply conditional formats through a range row by row

    Please see the original post again. I revised it so it should make more sense of what I'm trying to accomplish.
    Last edited by BeachRock; 10-10-2012 at 10:55 AM.

  4. #4
    Forum Contributor BeachRock's Avatar
    Join Date
    11-01-2011
    Location
    Oregon, United States
    MS-Off Ver
    Excel 2016
    Posts
    401

    Re: Loop and apply conditional formats through a range row by row

    Bump

    Revised original post to provide a better explanation of what I need.

  5. #5
    Forum Contributor BeachRock's Avatar
    Join Date
    11-01-2011
    Location
    Oregon, United States
    MS-Off Ver
    Excel 2016
    Posts
    401

    Re: Loop and apply conditional formats through a range row by row

    I found some code that might help in creating what I need. It appears to be applying CF to multiple rows but I don't know enough about VBA to really understand how to manipulate this to fit my requirements. Can anyone confirm that this is at least on the right track? And, what I might do to conform this to be what I need?

    Please Login or Register  to view this content.

  6. #6
    Valued Forum Contributor
    Join Date
    08-14-2012
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    573

    Re: Loop and apply conditional formats through a range row by row

    Hi Tony,
    You can try this formula in CF, instead of going to macro. For row 7 if(and($K$30>row()-7,$K$30<row()-6),1,0). row() function returns the row no. of current row. So it will be calculated as $K$30>7-7 and $K$30<7-6. When you copy the format to other rows, say row 8 it will become $K$30>8-7 and $K$30<8-6. I think this should solve your problem.

    Regards,
    Sindhu

  7. #7
    Forum Contributor BeachRock's Avatar
    Join Date
    11-01-2011
    Location
    Oregon, United States
    MS-Off Ver
    Excel 2016
    Posts
    401

    Re: Loop and apply conditional formats through a range row by row

    Hi Sindhus, thanks for the reply. I'm at work right now and the workbook is on my home PC. I'll test your solution this evening and post back to you about the result. It looks promising! Thanks.
    Last edited by BeachRock; 10-11-2012 at 11:27 AM.

  8. #8
    Forum Contributor BeachRock's Avatar
    Join Date
    11-01-2011
    Location
    Oregon, United States
    MS-Off Ver
    Excel 2016
    Posts
    401

    Re: Loop and apply conditional formats through a range row by row

    Hi Sindhus, I had some time here at work and tested your solution on a new workbook as it really doesn't matter if I test it on the one at home or another. On second look at your formula I realized it was matching both criteria at once instead of treating each separately since the result of each defines a separate CF. Instead, I broke it out into two formulas, =$K$30>row()-7 and =$K$30<row()-6 and assigned each to their respective CF as shown below.

    =$K$30>row()-7 Automatic font color and a border around each cell in the range
    =$K$30<row()-6 Font color white and no borders

    I then copied the range of cells (B7,D7:J7) and pasted formatting to the number of rows I needed done. It works perfectly. Whatever number that K30 changes to causes that many rows to change from white font and no borders to automatic font color with borders around each cell in the range. Thank you so much! You've saved me probably 2 months worth of work, at least. I must say though, I don't understand HOW this works since the formulas for each row I pasted CF to are the exact same for each row. Even if I pasted them to each consecutive row one by one, they remain the same formulas. If you don't mind, could you explain how/why this works?

  9. #9
    Valued Forum Contributor
    Join Date
    08-14-2012
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    573

    Re: Loop and apply conditional formats through a range row by row

    Hi Tony,
    Its good that your problem is resolved. The row() will return different values for each row. The formula remains the same for all the rows. However, the computed value will be different.

    Regards,
    Sindhu

+ 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