Excel conditional formatting if value is between or equal to values

conditional formattingmicrosoft excelmicrosoft-excel-2013

I have been trying to use conditional formatting to create a Gantt chart. So far I have not been very successful. Here is a screenshot of part of the chart:

The current formatting is hand made.

I want the cell to turn pink if it meets the following condition:

The week number (in row 4 of current column) must be greater than or equal to the start week (in current row column D) AND smaller than or equal to the end week (in current row column E).

I have tried to add conditional formatting with a formula. The formula I came up with looks like this:

=IF(AND(ADDRESS(4,COLUMN(),2)>=ADDRESS(ROW(),4,1);(ADDRESS(4,COLUMN(),2)>=ADDRESS(ROW(),5,1)))

Can someone tell me what I did wrong and how to fix it?

Best Answer

there is all sorts wrong, unfortunately!

ADDRESS gives you the address of the cell, not its value... so you are comparing whether an address is bigger/smaller
You have both checks as >= rather than one greater and one less than
You have a mix of , and ; (but I guess that is probably just editing onto this site!)

To enter the formula:

go into cell F6
Conditional formatting > new rule > use a formula...
=AND(F$4>=$D6,F$4<=$E6)
This format can then be copied and pasted into your gantt body

To explain:

in Conditional formatting, Excel will modify the cell references of non-absolute referenced cells. The formula you enter is the one for the top-left of the range that the rule applies to
We can fix the row or column with the $in the address

Even simpler

But if we consider that ISBLANK() function returns FALSE where there's a formula in a cell as well, we can simplify this even further by using this formula only:

=ISBLANK($D1)

Excel conditional formatting to highlight entire row if one date is not equal to another date in that row

1) Verify that both your date fields are in a valid date format and not text, which would not work. Easiest way is to right-click on them, "Format..." and change the format to Number. You should see the corresponding number like 40123 or similar.

Also, you can test it with the formula =D1>B1 on a blank cell, which should return TRUE if it's greater.

2) Select a row, say row 10, and create a new conditional formatting rule with this formula (the $ sign tells the rule to always check columns B and D specifically, since in the background the rule will do the calculation on each column indivually)

=$D10 > $B10

Save, check if your row is highlighted. If it works, extend the area of your rule ("Applies to") to the whole sheet or desired row.

P.S. You don't need to start with a row then extend the area, I just prefer this way for testing it as I go. If you make your rule with multiple rows selected, just make sure that the formula you type uses the row number of the FIRST row in your selection.

Best Answer

Related Solutions

Excel conditional formatting based on a formula

Even simpler

Excel conditional formatting to highlight entire row if one date is not equal to another date in that row

Related Question