0

My goal: I am trying to create an automated schedule using conditional formatting that, once a block of time has been assigned, will color code itself. The best and the only way I could figure how to do this is best explained in the screenshot below. 2 conditional formatting formulas so that if either "D" or "P" is input, it will block off a different amount of time.

My problem: I am trying to do this in such a way that I don't have to set conditional formatting for 500+ lines of hours in a week.

Solutions I have tried:

  • Copy/paste & Format Painter: I have not been able to figure out a combination of absolute references that will allow me to copy and paste my formatting in such a way that it will work given the parameters above.
  • Macros (1): My first pass with macros was a suggestion from this board, (link). Which, once again, I could not figure out how best to utilize it given that absolute references won't work. Seems to just be copying and pasting for me.
  • Macros (2): I tried to record myself setting the conditional formatting and using that as a macro, but it will only record the absolute position of the cell that I am applying it to, and I don't know how, if possible, to make it so that the target of the conditional formula shifts downward.

P.S. I'm also looking at possibly using some free appointment scheduling software if I can find one that works for my needs.

enter image description here

enter image description here

1 Answer 1

1

Apply your condition to $B$3:$J$500 and use the following conditional formula for "D":

=COUNTIF(B1:B3,"D")

For "P", you will need to apply a condition to $B$3:$J$3:

=B3="P"

And apply the following formula:

=COUNTIF(B1:B4,"P")

to $B$4:$J$500

2
  • OMG okay. You are amazing and you have me 90% of the way there, but maybe I'm just misinterpreting your instructions as I am getting some strange behavior. the solution for D is working flawlesly so for that, I already want to bu you a beer. For P, it's somehow formatting lines above where I want them to, shading B27 when it should start on B28, and I think that has to do with your 2nd instruction. This is where I am at right now. edit: gah, I dont know how to add a screen shot. I will add it to the original post. Jun 6, 2019 at 23:41
  • NOPE nevermind. I found my mistake.THANK YOU SO MUCH. You are a wizard and if somehow I ever get the opportunity. I will gladly buy you a drink. Cheers! Jun 6, 2019 at 23:47

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .