Shading Blank Cells Between Values?

[Guest]

Hello,
I have a program that I would like to keep track of pending leave for our
employees. There is a sheet for every employee. I enter leave start dates
and duration into these sheets.

There is a master sheet with all employees names that looks like a
calendar. A function places "Start" in the cell of the start date of leave,
and "End" in the cell of the end date of leave. I can use conditional
formatting to shade the cells that have "Start" and "End" in them, but I
can't figure out how to shade the blank cells between the two. Is this
possible?

Ex;
12/1 12/2 12/3 12/4 12/5
J. Doe Start End

How do I get it to shade 12/2, 12/3, and 12/4 along with 12/1 and 12/5?

-Thanks!

 

[Dave Peterson]

I used columns B:IV to hold my indicators.

I selected B2:IV999 and with B2 the activecell, I did this:

Format|conditional formatting|
formula is:
=AND(COUNTIF($B2:B2,"Start")>0,COUNTIF(B2:$IV2,"end")>0)

I gave it a nice pattern format and it seemed to work ok.

 

[Guest]

Dave,
Thanks so much for the reply! The formula does work, but I run into one
problem. The calendar is linear and it will highlight from the first "Start"
to the last "End"

Ex;
12/1 12/2 12/3 12/4 12/5 12/6 12/7 12/8 12/9
12/10
J. Doe Start End Start
End
* * * * * * *
* *
It will highlight all cells between 12/2 and 12/10. 12/5 and 12/6 should
not be highlighted. How can I write this exception?

I have tried, but I am not too familiar with the CountIf and And formulas
and how they are working in this operation.

-Thanks again!

 

[Dave Peterson]

I'm not sure this is the prettiest, but it seems to work ok:

=OR(B1="start",B1="end",MOD(COUNTIF(B1:$B1,"start")+COUNTIF(B1:$B1,"end"),2)=1)

With B1 the activecell.

If it's Start or End, shade it.

If the number of Starts + number of Ends is odd, then you're between start/end,
so shade it.

 

[Guest]

Dave,
Thank you so much!!! It works wonderfully! :-D

I'm going to take some time and try and figure out how this works so that
I can use it in other applications if the opportunity arises. Thanks again,
I really do appreciate your help!