I would like to highlight every 4th row of my spreadsheet and only highlight the cells in that row that are greater than the current month. My header in the spreadsheet is the month/yr. I was thinking an INDEX formula, but the date part is where I am stumped. I have attached an example sheet to better explain.
Last edited by dta1984; 12-08-2011 at 11:23 AM.
See attached. (I think you meant that the months starting in January would be in 2012, not 2011.)
Yep, that's what I need. When I transfer the formula and edit it to my needs onto my sheet, I can't get it to work. My data starts at G6, then goes all the way to row AF. Can you explain what the cell A1 and the mod part of your formula do?
Is there a way to edit the formula so it applies to cells with data in them only?
The formula, when it applies to a range like that, needs to be the formula that would apply to the upper left cell of the range. So for example instead of referring to B$1, it would refer to G$5 (I'm going from memory here but hopefully you get the idea). For applying only to cells with data, you would add another condition inside the AND:
G6<>""
I got the formula to work on my sheet...but.... it wants to start the shading in the row directly beneath my month/yr row. I'll attach a modified example. I just need to adjust where it starts the shading.
If i change the =0 to an =1, I get it to land on the cell I want, but it starts at the top of the worksheet. I want it to start at row 7. I have been messing with it for a while and have had no luck. Also, where do i add the <>"" part to only highlight cells with data in them?
=AND($A1<>"",MOD(ROW()-2,4)=1,DATE(YEAR(G$5),MONTH(G$5),1)>TODAY())
The forum may be experiencing some problems with attachments. I just downloaded my own attachment and Excel can't recognize the file (I am on a different computer than the one I was on when I wrote it). And I downloaded your attachment above but it looks like a completely different file than the one I remembered looking at before.
At any rate, I think you want the formula to look like this. This is for nonblank data located in column G, every fourth row starting in row 7, and the conditional formatting range also starts in G7:
=AND(G7<>"",ROW()>=7,$A7<>"",MOD(ROW()-7,4)=0,DATE(YEAR(G$5),MONTH(G$5),1)>TODAY())
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks