title: VBA makes Excel slow? Or conditional formatting?
body: <p>Creating a pseudo Gantt project plan in Excel. For those employees without MS Project, I ran into the following problem. The Excel sheet becomes very sluggish. I am not sure if it is caused by the VBA function I used or the conditional formatting.</p>
  
  <p>The table starts with the task details
  <em>task, duration, taskStart, taskEnd</em>.</p>
  
  <p>and continues with the calendar part including the fields weekStart and weekEnd.
  In each calendar cell is a formular</p>
  
  <pre><code>=PseudoGantt(task;duration;taskStart;taskEnd;weekStart;weekEnd)
  </code></pre>
  
  <p>The macro looks like the following:</p>
  
  <pre><code>Function PseudoGantt(taskName As String, taskLength As Integer, taskStart As Date, taskEnd As Date, weekStart As Date, weekEnd As Date) As String
      PseudoGantt = Switch( _
          taskName = "", "empty", _
          taskStart = taskEnd And taskStart &gt;= weekStart And taskEnd &lt;= weekEnd, "milestone", _
          taskLength = 0 And taskStart &gt;= weekStart And taskStart &lt; weekEnd, "summarytask", _
          taskLength = 0 And taskEnd &lt;= weekEnd And taskEnd &gt; weekStart, "summarytask", _
          taskLength = 0 And taskStart &lt; weekStart And taskEnd &gt; weekEnd, "summarytask", _
          taskStart &gt;= weekStart And taskStart &lt; weekEnd, "start", _
          taskEnd &lt;= weekEnd And taskEnd &gt; weekStart, "end", _
          taskStart &lt; weekStart And taskEnd &gt; weekEnd, "continue", _
          True, "empty" _
      )
  End Function
  </code></pre>
  
  <p>This will fill the cell with a Gantt state the week is in, which I use as condition for the conditional formatting to color the cell appropriately.</p>
  
  <p>This works, but as I said, it makes Excel very slow.</p>
  
  <p>I thought about replacing the conditional formatting within the VBA custom function, but they do only return values and cannot change the cell color or the like.</p>
  
  <p>Question: </p>
  
  <ol>
  <li>is it the custom function or the conditional formatting what makes Excel slow?</li>
  <li>How can I color the cells from VBA?</li>
  <li>Should I rather leave the calendar cells empty and let VBA do the coloring without the workaround to write and content into the cells?</li>
  <li>If your answer to 3. is "yes". How to do that?</li>
  </ol>
  
  <p>Thanks for listening.</p>
  
  <p><em>Side note:</em> sorry, can't post the screenshot yet (Anti-Spam-Filter)</p>