Forum Discussion

Copper Contributor

Sep 24, 2017

Solved

Trying to use conditional formatting to color the space between two data entries.

Here is an example of what I am trying to do, https://docs.google.com/spreadsheets/d/1ejUJcvITfT31VSLi8d3SS1tyon1MP_6lCJUIRO629Js/edit#gid=0 I colored the cells just as an example. This would be ...

Formulas & Functions

Need Help

IngeborgHawighorst
Sep 24, 2017
Hello,
I suggest you use a helper column with this formula, starting in F3 and copied down:

=IF(ISNUMBER(B3),"occupied",IF(ISNUMBER(D2),"vacant",IF(F2="occupied","occupied","vacant")))

That will give you a column of values of "occupied" or "vacant". You can then use conditional formatting with a formula for columns B to E. (By the way, avoid merging cells like that. It can lead to problems).

The conditional formatting formula is

=$F3="occupied"

You can then hide column F, so it does not show in the sheet.

IngeborgHawighorst

MVP

Sep 24, 2017

Hello,

I suggest you use a helper column with this formula, starting in F3 and copied down:

=IF(ISNUMBER(B3),"occupied",IF(ISNUMBER(D2),"vacant",IF(F2="occupied","occupied","vacant")))

That will give you a column of values of "occupied" or "vacant". You can then use conditional formatting with a formula for columns B to E. (By the way, avoid merging cells like that. It can lead to problems).

The conditional formatting formula is

=$F3="occupied"

You can then hide column F, so it does not show in the sheet.

Soren

Copper Contributor

Sep 25, 2017

May I ask what does this part of the formula do?

IF(F14="occupied","occupied","vacant"

Thanks!

IngeborgHawighorst
MVP
Sep 25, 2017
Hi Soren,

this part IF(F2="occupied","occupied","vacant"))) checks if the cell above has the word "occupied" and if so, it repeats it. If an end of the occupation has been entered, the formula won't even get to that evaluation, because this IF(ISNUMBER(D2),"vacant", will return the "vacant" flag and then exit the formula.