Databases
StackOverflow2013
Postsdbo
POExcel changes conditional formatting formula
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POExcel changes conditional formatting formula
    text
    copied!<p>I have a table containing a lot of cells representing a time line (one cell per minute, very small width) and I want to visualize operations, containing of three phases, in this table. (There can be several operations in one line, representing one operating room)</p> <p>For example, if the preparation starts at 10:00 and the real operation starts at 10:23, all 23 cells between those times should get red, and the next 55, representing the actual operation between 10:23 and 11:18, should be green, and so on, like this:</p> <pre><code> 17 18 19 20 21 22 23 00 01 02 03 04 | | | | | | | | | | | | OR1______________++++=================****______________++=========***____________ OR2______________________+++++======================*****_________________________ </code></pre> <p>Each of the cells is formatted if a time value for it (taken from an extra row) is between two of the start/end values, with special cases for numbers around midnight.</p> <p>To automate this (there are a lot of operations) I wrote a macro which runs through the table of the times (four time columns for the starts and endings of the three phases and one representing the operation room) and assigns each column in the big table (with the colored cells) three conditional formats, which should work correctly (I checked them manually):</p> <pre><code>T = time.Address(RowAbsolute:=True, ColumnAbsolute:=False) Z1 = t1.Address Z2 = t2.Address Z3 = t3.Address Z4 = t4.Address fnc1 = "=OR(AND(" &amp; Z1 &amp; "&lt;=" &amp; T &amp; ";" &amp; T &amp; "&lt;" &amp; Z2 &amp; ");AND(" &amp; Z2 &amp; "&lt;" &amp; Z1 &amp; ";" &amp; Z1 &amp; "&lt;=" &amp; T &amp; ");AND(" &amp; T &amp; "&lt;" &amp; Z2 &amp; ";" &amp; Z2 &amp; "&lt;" &amp; Z1 &amp; "))" fnc2 = "=OR(AND(" &amp; Z2 &amp; "&lt;=" &amp; T &amp; ";" &amp; T &amp; "&lt;" &amp; Z3 &amp; ");AND(" &amp; Z3 &amp; "&lt;" &amp; Z2 &amp; ";" &amp; Z2 &amp; "&lt;=" &amp; T &amp; ");AND(" &amp; T &amp; "&lt;" &amp; Z3 &amp; ";" &amp; Z3 &amp; "&lt;" &amp; Z2 &amp; "))" fnc3 = "=OR(AND(" &amp; Z3 &amp; "&lt;=" &amp; T &amp; ";" &amp; T &amp; "&lt;" &amp; Z4 &amp; ");AND(" &amp; Z4 &amp; "&lt;" &amp; Z3 &amp; ";" &amp; Z3 &amp; "&lt;=" &amp; T &amp; ");AND(" &amp; T &amp; "&lt;" &amp; Z4 &amp; ";" &amp; Z4 &amp; "&lt;" &amp; Z3 &amp; "))" Dim currentLine As Range Set currentLine = Range(Cells(dline, data.Column), Cells(dline, data.Column).Offset(0, width)) Set frmt1 = currentLine.FormatConditions.Add(XlFormatConditionType.xlExpression, Formula1:=fnc1) Set frmt2 = currentLine.FormatConditions.Add(XlFormatConditionType.xlExpression, Formula1:=fnc2) Set frmt3 = currentLine.FormatConditions.Add(XlFormatConditionType.xlExpression, Formula1:=fnc3) frmt1.Interior.Color = RGB(255, 0, 0) frmt2.Interior.Color = RGB(0, 255, 0) frmt3.Interior.Color = RGB(0, 0, 255) </code></pre> <p>'t1' .. 't4' are the cells of the table with start/end times, 'time' is the extra row with the literal time for each column in the visualization table (10:01, 10:02,..., with the same width as 'data'), 'data' is the visualization table and 'dline' is the number of the currently formatted row, calculated depending on the operation room. 'width' is now usually 300, but in general 60 * the hours shown (so just the number of minutes of that time).</p> <p>This whole thing works without errors, and it produces the right formatting strings (I checked fnc1..fnc3 in debugging mode). They look like this, which they should do:</p> <pre><code>=OR(AND($KR$10&lt;=C$13;C$13&lt;$KS$10);AND($KS$10&lt;$KR$10;$KR$10&lt;=C$13);AND(C$13&lt;$KS$10;$KS$10&lt;$KR$10)) </code></pre> <p>C13 is the first cell of time, and KR10 and KS10 are 't1' and 't2' for this operation (the start of preparation, and the start of actual operating/end of preparation).</p> <p>But: as soon as the format strings are saved to excel (macro finished), they are not the same anymore. Usually something like</p> <pre><code>=OR(AND($KR$10&lt;=XCS$13;XCS$13&lt;$KS$10);AND($KS$10&lt;$KR$10;$KR$10&lt;=XCS$13);AND(XCS$13&lt;$KS$10;$KS$10&lt;$KR$10)) </code></pre> <p>comes out - the value of the 'time' cell, which is the only relative one, changes to strange, high values ('XCS', 'WTC', 'XBR'...). If I make the row relative, too, it also canges (I got numbers like 163552 with that).</p> <p>This seems to be independent of the macro, since I first tried the whole thing in IronPython, which worked the same way (I actually translated the macro from that code) and produced the same error. </p> <p>I even sometimes got the same kind of error when I was assigning the formula by hand for testing, so I strongly suspect Excel of being the real error...</p> <p>Now, since I couldn't find anyhing about a similar error, could that have to to with memory? Like, too many cells? Or something specific to my computer/installation/code/...? </p> <p>I use Excel 2007. The cells of the 'data' table are not specially formatted, the times are all hh:mm:ss.</p> <p>I hope I made it clear enough how the whole stuff works; otherwise, just ask for more code or whatever information is needed. Thanks for any hints, I've been stuck at this for for a week now and I'm running out of ideas... </p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload