• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Row() and Column() Fail in Conditional Formatting

Ed Burke

New Member
Hello all,

I'm attempting some relatively complex conditional formatting. I've broken it down into several rules, but I keep running in a brick wall with the use of the two referenced functions within conditional formatting.

Here is one rule for the the conditional formatting of one cell:
Code:
=IF(OFFSET(A3,INT((ROW(A3)-2)/OffsetV)*OffsetV-(ROW(A3)-2-1),INT(COLUMN(A3)/OffsetH)*OffsetH+(OffsetH-COLUMN(A3)))<A1,TRUE,FALSE)

OffsetV and OffsetH are named ranges (single cell). Cell A3 is the cell this conditional format is contained within (no absolute references because I need the cell address to carry across the spreadsheet). Cell A1 contains the date value I'm checking against. The result of the OFFSET() function is the date I need to check against the date value in Cell A1.

These formula work perfectly in any cell on the spreadsheet, but fail miserably when input into conditional formatting. Through various experimentation, I found that if I remove the Row() and Column() functions and replace them with the result (i.e. Replace Row(A3) with 3), the conditional formatting works fine. However, if I use the Row() and Column() functions in a simpler conditional format (i.e. IsOdd(Row()), it works fine.

Any idea why it might be failing only within complex conditional formatting?

Thanks,
Ed
 
Dear Ed
Please upload a sample file with your structured data
Nazmul,

I won't be able to upload a sample without major modification (I might be able to work on that if I can't solve this problem). To give you an idea of why I structured the conditional formatting like this, I'll try to describe my worksheet layout.

I have a worksheet containing 42 blocks of cells (each block is 6 cells wide and 12 cells tall). Each block represents one calendar day of one month and are laid out as such (i.e. six weeks, seven blocks per week). The six weeks represent all possible combinations of dates within a given month. The top right cell of each block represents a date. It is a complete date (no time), but displays as only the day (i.e. date/time format "d"). I have various cells within each block that need to be conditionally formatted based on how that block's date compares to the date entered into cell A1. The functions within the conditional formatting finds the appropriate date for each block (calendar day) in order to compare it correctly.

I tried using various combinations of absolute references to make it much easier and that worked well, but did not translate correctly when I tried to copy the conditional formatting across all blocks. I had to go into each block and modify the individual conditional formatting. Not a big deal for one month, but I have to modify twelve months worth of blocks. This conditional formatting should work, and would allow me to copy it across all sheets without modfiying individual blocks.

Thanks,
Ed
 
Ed,

Perhaps you could try without the IF as follows:

=OFFSET(A3,INT((ROW(A3)-2)/OffsetV)*OffsetV-(ROW(A3)-2-1),INT(COLUMN(A3)/OffsetH)*OffsetH+(OffsetH-COLUMN(A3)))<A1

This is a logical expression that will result in either TRUE or FALSE and should work in conditional formatting.

Hope that helps.

Regards,
Ken
 
Ken.

Awesome. I tried this minutes after my last failure and it worked (mostly). Makes me wonder if there a certain limit to nesting functions within conditional formatting statements. With the IF, the ROW became the third nested function.

Also, the reason I say "mostly" is because when I pasted the conditional formatting across my calendar it worked on almost every cell (for each block, it didn't take on two or three cells, but never the same two or three cells in each block), but there some stingy cells that just won't take it, even though there are no other conditional formatting statements and, otherwise, the cell is completely blank with no formula or formatting. Any ideas on this one?

Thanks,
Ed
 
Also, the reason I say "mostly" is because when I pasted the conditional formatting across my calendar it worked on almost every cell (for each block, it didn't take on two or three cells, but never the same two or three cells in each block), but there some stingy cells that just won't take it, even though there are no other conditional formatting statements and, otherwise, the cell is completely blank with no formula or formatting.

Problem solved. There was a misplaced '-1.' Should've been within a set of parenthesis and it was not. This little change solved all of the issues with the conditional formatting "not taking" to particular cells.

Thanks for the help!
 
Back
Top