Conditional formatting using AND() function

excel excel-formula conditional-formatting
some user picture some user · Dec 12, 2011 · Viewed 121.4k times · Source

I'm trying conditional formatting on a sheet. I need to fill the cells with a certain color according to the following conditional statement:

=AND((INDIRECT(ADDRESS(4;COLUMN()))>=INDIRECT(ADDRESS(ROW();4)));(INDIRECT(ADDRESS(4;COLUMN()))<=INDIRECT(ADDRESS(ROW();5))))

When I try the statements in the AND() function separately, they seem to work, but when I put them together in the function I don't see any formatting happening.

Here is some background: Row 4 of the "current column" has a date (DATE1) in it. There are also dates on the D and E columns of the "current row" (DATE2 and DATE3). So, I would like to fill the cell with a color if DATE1 is between DATE2 and DATE3.

I cannot see why the formula is not working. Any help is much appreciated.

Update (Dec 13, 2011):

I implemented a function that I call from the cells I need this functionality. The function returns integer values. Then conditional formatting only uses the integers in the cells. This way, the conditional formatting is less complicated. I'm passing INDIRECT(ADDRESS(ROW();COLUMN())) into the function I implement. So, I have all the information I need when working on relative and/ or absolute cells. Would be great to know a simpler way to pass the current cell as range into the function.

Note: ActiveCell didn't seem to work for me. It uses the data from the cell which is selected by the time the function is run. That's not what I'm looking for. I could of course pass the cell itself (as in A4, B7, etc.) but I'm not sure if it really matters in terms of performance.

Thanks to all of you who responded to my question.

Answer

James picture James · Feb 16, 2012

I was having the same problem with the AND() breaking the conditional formatting. I just happened to try treating the AND as multiplication, and it works! Remove the AND() function and just multiply your arguments. Excel will treat the booleans as 1 for true and 0 for false. I just tested this formula and it seems to work.

=(INDIRECT(ADDRESS(4,COLUMN()))>=INDIRECT(ADDRESS(ROW(),4)))*(INDIRECT(ADDRESS(4,COLUMN()))<=INDIRECT(ADDRESS(ROW(),5)))

Related questions

Conditionally formatting if multiple cells are blank (no numerics throughout spreadsheet )

I have created a spreadsheet in Excel and am attempting to use Conditional Formatting to highlight a cell or row if any or all of the cells in the last four columns are blank. My columns consist of name of …

Read More
Conditionally formatting cells if their value equals any value of another column

I have data in the A and B columns. B column's data is mostly duplicates of A's data, but not always. For example: A Budapest Prague Paris Bukarest Moscow Rome New York B Budapest Prague Los Angeles Bukarest I need …

Read More
Conditional formatting if a cell does not equal one of three values

I have a dataset as below: I want to apply conditional formatting that will format a cell if the value in that cell is not 100, 50 or 0 (ie I want it to pick up A5 and format it) I'm new to …

Read More