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:


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
Related Question