Conditional formatting using AND() function

Question

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.

James · Accepted Answer · 2012-05-16T14:02:38.033

41

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)))

edited May 16 '12 at 14:02

answered Feb 15 '12 at 23:13

James

1,736
2
23
25

I haven't tried the solution, but it makes sense. I'll try it as soon as I can. – some user Feb 17 '12 at 10:04
@davient Both of your edits were wrong. First, the address function takes the row as the first argument. Row 4 IS row "D". What you have given is column 4 & 5 on the current row. Also, conditional formatting applies rules on cells. To highlight a whole row the rule would have to evaluate to true for the whole row. As for the logical operators, False has a value of 0 and True has a value of 1. TRUE - TRUE = 1 - 1 = FALSE is not a valid OR operation. Also, TRUE / FALSE = 1 / 0 = ERROR is not a valid AND operation. I have verified this is how it works in excel. – James May 16 '12 at 14:00
Is there a similar solution for the Excel OR() function, or does that still work, in situations where the overall formula gets complex? – vwfreak May 10 '16 at 18:38
2

@vwfreak Use addition for OR() – James May 14 '16 at 04:46

score 4 · Answer 2 · answered Dec 12 '11 at 11:38

4

You can use a much simpler formula. I just created a new workbook to test it.

Column A = Date1 | Column B = Date2 | Column C = Date3

Highlight Column A and enter the conditional formatting formula:

=AND(A1>B1,A1<C1)

answered Dec 12 '11 at 11:38

Lee Tickett

5,847
8
31
55

Thanks for the response, but it's not possible to put DATE1 to the same row as the others. – some user Dec 12 '11 at 11:43
Missed that bit sorry. So is the same true for every row? The DATE1 value is in the same column 4 rows below? I'm struggling to visualize your data. Can you attach the worksheet or a screenshot? – Lee Tickett Dec 12 '11 at 11:52
In a row, there are two dates DATE2 (start) and DATE3 (end). And then, starting from row 10, every cell in that row has a date at row 4 of the corresponding column. I'll try to attach a screenshot later today. – some user Dec 12 '11 at 12:12
I tried to mimic your scenario and simply had to adjust the formula to =AND(A1>B5,A1 – Lee Tickett Dec 12 '11 at 12:28

score 1 · Answer 3 · edited Jun 24 '13 at 14:47

1

I had a similar problem with a less complicated formula:

= If (x > A & x <= B)

and found that I could Remove the AND and join the two comparisons with +

  = (x > A1) + (x <= B1)        [without all the spaces]

Hope this helps others with less complex comparisons.

edited Jun 24 '13 at 14:47

sebataz

1,025
2
11
35

answered Jun 24 '13 at 14:21

David

11
1

score 0 · Answer 4 · answered Apr 03 '15 at 13:18

I am currently responsible for an Excel application with a lot of legacy code. One of the slowest pieces of this code was looping through 500 Rows in 6 Columns, setting conditional formatting formulae for each. The formulae are to identify where the cell contents are non-blank but do not form part of a Named Range, therefore referring twice to the cell itself, originally written as:

=AND(COUNTIF(<rangename>,<cellref>)=0,<cellref><>"")

Obviously the overheads would be much reduced by updating all Cells in each Column (Range) at once. However, as noted above, using ADDRESS(ROW(),COLUMN(),n) does not work in this circumstance, i.e. this does not work:

=AND(COUNTIF(<rangename>,ADDRESS(ROW(),COLUMN(),1))=0,ADDRESS(ROW(),COLUMN(),1)<>"")

I experimented extensively with a blank workbook and could find no way around this, using various alternatives such as ISBLANK. In the end, to get around this, I created two User-Defined Functions (using a tip I found elsewhere on this site):

Public Function returnCellContent() As Variant

  returnCellContent = Application.Caller.Value

End Function

Public Function Cell_HasContent() As Boolean

  If Application.Caller.Value = "" Then
    Cell_HasContent = False
  Else
    Cell_HasContent = True
  End If

End Function

The conditional formula is now:

=AND(COUNTIF(<rangename>,returnCellContent()=0,Cell_HasContent())

which works fine.

This has sped the code up, in Excel 2010, from 5s to 1s. Because this code is run whenever data is loaded into the application, this saving is significant and noticeable to the user. It's also a lot cleaner and reusable.

I've taken the time to post this because I could not find any answers on this site or elsewhere that cover all of the circumstances, whilst I'm sure that there are others who could benefit from the above approach, potentially with much larger numbers of cells to update.

score 0 · Answer 5 · answered Dec 20 '17 at 21:24

Same issues as others reported - using Excel 2016. Found that when applying conditional formulas against tables; AND, multiplying the conditions, and adding the conditions failed. Had to create the TRUE/FALSE logic myself:

=IF($C2="SomeText",0,1)+IF(INT($D2)>1000,0,1)=0

score 0 · Answer 6 · answered Dec 12 '11 at 11:34

0

This is probably because of the column() and row() functions. I am not sure how they are applied in conditional formatting. Try creating a new column with the value from this formula and then use it for your formatting needs.

answered Dec 12 '11 at 11:34

ivan

390
3
13

Thank you for the response. I'll try it and report back. – some user Dec 12 '11 at 11:43
I ended up implementing the formula in a function where I return integers for different reasons. It was pretty easy getting DATE2 and DATE3 by using something like Cells(ActiveCell.Row, 4).Value. But when it comes to getting DATE1, Cells(4, ActiveCell.Column).Value doesn't seem to do the trick. Maybe it's because, the function I have written is not called every time I update DATE2 or DATE3? I'm stuck at a point where this sometimes works and sometimes doesn't Any ideas? – some user Dec 13 '11 at 14:44
Did you declare your function with Application.Volatile? – ivan Dec 15 '11 at 08:30

score 0 · Answer 7 · answered Dec 12 '11 at 14:36

0

COLUMN() and ROW() won't work this way because they are applied to the cell that is calling them. In conditional formatting, you will have to be explicit instead of implicit.

For instance, if you want to use this conditional formating on a range begining on cell A1, you can try:

`COLUMN(A1)` and `ROW(A1)`

Excel will automatically adapt the conditional formating to the current cell.

answered Dec 12 '11 at 14:36

JMax

26,109
12
69
88

Thanks for the response. I ended up updating the value in the cell so that conditional formatting will be done according to cell values. – some user Dec 13 '11 at 14:45
glad you eventually found a solution. You can post it as an answer and accept it so that the topic will be somehow closed - see http://stackoverflow.com/faq#howtoask – JMax Dec 13 '11 at 15:15
When I'm completeley done with it, I'll do so. There still are hickups. For example, the ActiveCell function returns the selected cell while running a script. Which doesn't make any sense. Therefore I'm looking for something that would return the current cell where the function is executed. I'll come back with an answer soon... – some user Dec 13 '11 at 15:57

Conditional formatting using AND() function

7 Answers7

Linked

Related