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.

Please see the original post again. I revised it so it should make more sense of what I'm trying to accomplish.

Bump

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

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.

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.

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?