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     gt  INDIRECT ADDRESS ROW   4     INDIRECT ADDRESS 4 COLUMN     lt  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

User · Accepted Answer

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     gt  INDIRECT ADDRESS ROW   4     INDIRECT ADDRESS 4 COLUMN     lt  INDIRECT ADDRESS ROW   5

User · Answer

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

User · Answer

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 gt B1 A1 lt C1

User · Answer

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  lt rangename gt   lt cellref gt   0  lt cellref gt  lt  gt       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  lt rangename gt  ADDRESS ROW   COLUMN   1   0 ADDRESS ROW   COLUMN   1  lt  gt       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  lt rangename gt  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

User · Answer

I had a similar problem with a less complicated formula        If  x  gt  A  amp  x  lt   B     and found that I could Remove the AND and join the two comparisons with           x  gt  A1     x  lt   B1          without all the spaces    Hope this helps others with less complex comparisons

User · Answer

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  gt 1000 0 1  0

User · Answer

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

[excel] Conditional formatting using AND() function

Examples related to excel

Examples related to excel-formula

Examples related to conditional-formatting