Conditional Formatting Issue

[3G]

Hello there-

I've searched the box and read countless threads, but, still having the same issue w/conditional formatting.


I've made a small Gantt inside a dashboard, and want to fill the cells when a date is between the start & end date(s).


My formula within the Gantt is great, and working as expected: =IF(AND(H$22>Sheet1!$B4,H$22<Sheet1!$C4),"1",""). I have "1"s in all of the necessary cells. However, when I go to apply conditional formatting, it doesn't work. I've used 3 different approaches:

1. Simply setting when cell value = 1

2. Creating a named range of the gantt, when equal to 1 using a formula (i.e. =Gantt=1

3. Using the actual range of the gantt as a formula =H23:Y33=1


Any ideas?


Thanks

 

[SirJB7]

Hi, 3G!

Any chance of uploading as sample file?

Regards!

 

[NARAYANK991]

Hi ,


Just change your formula to :


=IF(AND(H$22>Sheet1!$B4,H$22<Sheet1!$C4),1,"")


You are using a text value by "1" , so checking to see if it is equal to the numeric 1 will not give the expected result.


Narayan

 

[3G]

Hi SirJB7!

Unfortunately that is blocked at work :-(.


Narayan- Thank you for this! No wonder! The irony is that I actually updated the formula to


VALUE(=IF(AND(H$22>Sheet1!$B4,H$22<Sheet1!$C4),"1","")) and it worked, but with the number of cells & reference updates I'd have to make, I'm SO thankful for your solution.


Thanks so much. You guys are so generous with your time.


3G

 

[SirJB7]

Hi, 3G!

Glad you solved it. Thanks for your feedback sharing your solution and for your kind words too. And welcome back whenever needed or wanted.

Regards!