Excel更改条件格式公式 [英] Excel changes conditional formatting formula
问题描述
例如，如果准备从10:00开始，实际操作从10:23开始，那些时间之间的所有23个单元格都应该变红，下一个55代表10:23和11:18之间的实际操作应该是绿色的，如下所示：
17 18 19 20 21 22 23 00 01 02 03 04
| | | | | | | | | | | |
OR1 ______________ ++++ ================= **** ______________ ++ ========= *** ____________
OR2 ______________________ +++++ ====================== ***** _________________________
如果每个单元格的时间值（从一个额外的行获取）位于两个开始/结束值之间，则每个单元格都将被格式化，特殊情况在午夜左右。为了自动化（有很多操作），我写了一个宏，它遍历时间表（四个时间列为三个开始和结尾的时间列表）阶段，一个代表操作室），并分配大表格（带有彩色单元格）的三个条件格式的每个列，这些格式应该正常工作（我手动检查它们）：
T = time.Address（RowAbsolute：= True，ColumnAbsolute：= False）
Z1 = t1.Address
Z2 = t2.Address
Z3 = t3.Address
Z4 = t4.Address
fnc1 == OR（AND（&&< T&;&a熔点; T& < &安培; Z2& ）; AND（&;&&&;&）; AND（& T&
fnc2 == OR（AND（&Z2&）& =& T&;& T&& Z3&）; AND（& Z3&& Z2& &< =& T&）; AND（& T&& Z3&;& Z3&" Z2& ）
fnc3 == OR（AND& ; AND（& Z4&&Z3;& Z3=& T）; AND（& T & Z4&;& Z4&& Z3&））$ $ $ $ $ $ $ $ $ $ $ $ $ $ $设置frmt1 = currentLine.FormatConditions.Add（XlFormatConditionType.xlExpression，Formula1：= fnc1）$（$） b $ b设置frmt2 = curre ntLine.FormatConditions.Add（XlFormatConditionType.xlExpression，Formula1：= fnc2）
设置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）
't1'..'t4'是表开始/结束时间的单元格，'时间'是额外的行的可视化表中的每列的字面时间（10:01，10:02，...，与'data'相同的宽度），'data'是可视化表，'dline'是当前格式化的行，根据操作室计算。 'width'现在通常为300，但通常为60 *显示的时间（因此只是当时的分钟数）。
这整个事情没有错误，并且它生成正确的格式化字符串（我在调试模式下检查fnc1..fnc3）。他们看起来像这样，他们应该做：
= OR（AND（$ KR $ 10 <= C $ 13; C $ 13 （$ KS $ 10）; AND（$ KS $ 10 <$ KR $ 10; $ KR $ 10 <= 13 $ 13）; AND（C $ 13 <$ KS $ 10; $ KS $ 10 <$ KR $ 10） $ b C13是时间的第一个单元格，KR10和KS10是此操作的t1和t2 （准备开始，实际操作开始/准备结束）。
但是，一旦格式字符串保存到excel（宏完成），他们不一样了通常情况下，如
= OR（AND（$ KR $ 10 <= XCS $ 13; XCS $ 13 <$ KS $ 10）; AND（$ KS $ 10 <$ KR $ 10; $ KR $ 10 <= XCS $ 13）; AND（XCS $ 13 <$ KS $ 10; $ KS $ 10 <$ KR $ 10））
出来 - 唯一相对的单元格的值更改为奇怪的高值（XCS 'WTC'，'XBR'...）。如果我也做相对行，它也可以（我得到数字，如163552）。
这似乎是独立于宏，因为我第一次尝试IronPython中的整个事情，它的工作方式相同（我实际上是从该代码中翻译出宏）并产生了相同的错误。
当我用手分配公式进行测试时，我甚至有时会遇到同样的错误，所以我强烈怀疑Excel是真正的错误...
现在，由于我找不到有关类似错误的内容，可能需要使用内存吗？喜欢，细胞太多？或特定于我的电脑/安装/代码/ ...的东西？
我使用Excel 2007.数据表的单元格没有特殊格式，时间全部是hh：mm：ss。
我希望我足够清楚整个东西如何工作;否则，只需要更多的代码或任何需要的信息。感谢任何提示，我已经被困在一个星期了，现在我用完了...
（信用到蒂姆·威廉姆斯）
该问题似乎需要在活动单元格的位置执行某些操作，在此过程中更改。以下代码工作并将被使用（我也修正了我以前看不到的另一个错误）：
T =时间。地址（True，False）
'这里我最初忘记更新行（与问题无关）
Z1 = Cells（tline，t1.Column）.Address
Z2 = Cells（tline ，t2.Column）.Address
Z3 = Cells（tline，t3.Column）.Address
Z4 = Cells（tline，t4.Column）.Address
fnc1 = = OR（AND（&&& T&;&& Z2&）; AND（& Z2& &;&&< =& T&）; AND（& T&&Z2 ; Z2&& Z1&））
fnc2 == OR（AND（&Z2&=& T&& Z3&）; AND（& Z3&& Z2;& Z2&& ）; AND（& T&& Z3&;& Z3&& Z2&））
fnc3 == OR （AND（&Z3& < =& T& ; &安培; T& < &安培; Z4& ）; AND（& Z4&Z3;& Z3，=& T&）; AND（& T& "& Z4&;& Z4&& Z3&））
Dim currentLine As Range
Set currentLine = Range单元格（dline，data.Column），单元格（dline，data.Column）.Offset（0，width））
currentLine.Select'< - 必要的更改解决问题
设置frmt1 = currentLine.FormatConditions.Add（XlFormatConditionType.xlExpression，Formula1：= fnc1）
设置frmt2 = currentLine.FormatConditions.Add（XlFormatConditionType.xlExpression，Formula1：= fnc2）
设置frmt3 = currentLine.FormatConditions。添加（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）
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)
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:
17 18 19 20 21 22 23 00 01 02 03 04
| | | | | | | | | | | |
OR1______________++++=================****______________++=========***____________
OR2______________________+++++======================*****_________________________
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.
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):
T = time.Address(RowAbsolute:=True, ColumnAbsolute:=False)
Z1 = t1.Address
Z2 = t2.Address
Z3 = t3.Address
Z4 = t4.Address
fnc1 = "=OR(AND(" & Z1 & "<=" & T & ";" & T & "<" & Z2 & ");AND(" & Z2 & "<" & Z1 & ";" & Z1 & "<=" & T & ");AND(" & T & "<" & Z2 & ";" & Z2 & "<" & Z1 & "))"
fnc2 = "=OR(AND(" & Z2 & "<=" & T & ";" & T & "<" & Z3 & ");AND(" & Z3 & "<" & Z2 & ";" & Z2 & "<=" & T & ");AND(" & T & "<" & Z3 & ";" & Z3 & "<" & Z2 & "))"
fnc3 = "=OR(AND(" & Z3 & "<=" & T & ";" & T & "<" & Z4 & ");AND(" & Z4 & "<" & Z3 & ";" & Z3 & "<=" & T & ");AND(" & T & "<" & Z4 & ";" & Z4 & "<" & Z3 & "))"
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)
'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).
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:
=OR(AND($KR$10<=C$13;C$13<$KS$10);AND($KS$10<$KR$10;$KR$10<=C$13);AND(C$13<$KS$10;$KS$10<$KR$10))
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).
But: as soon as the format strings are saved to excel (macro finished), they are not the same anymore. Usually something like
=OR(AND($KR$10<=XCS$13;XCS$13<$KS$10);AND($KS$10<$KR$10;$KR$10<=XCS$13);AND(XCS$13<$KS$10;$KS$10<$KR$10))
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).
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.
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...
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/...?
I use Excel 2007. The cells of the 'data' table are not specially formatted, the times are all hh:mm:ss.
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...
(Credit goes to Tim Williams)
The issue seems to have to do something with the location of the active cell, changing during the process. The following code works and will be used (I also corrected another error I couldn't see before):
T = time.Address(True, False)
' here I initially forgot to update the row (not related to problem)
Z1 = Cells(tline, t1.Column).Address
Z2 = Cells(tline, t2.Column).Address
Z3 = Cells(tline, t3.Column).Address
Z4 = Cells(tline, t4.Column).Address
fnc1 = "=OR(AND(" & Z1 & "<=" & T & ";" & T & "<" & Z2 & ");AND(" & Z2 & "<" & Z1 & ";" & Z1 & "<=" & T & ");AND(" & T & "<" & Z2 & ";" & Z2 & "<" & Z1 & "))"
fnc2 = "=OR(AND(" & Z2 & "<=" & T & ";" & T & "<" & Z3 & ");AND(" & Z3 & "<" & Z2 & ";" & Z2 & "<=" & T & ");AND(" & T & "<" & Z3 & ";" & Z3 & "<" & Z2 & "))"
fnc3 = "=OR(AND(" & Z3 & "<=" & T & ";" & T & "<" & Z4 & ");AND(" & Z4 & "<" & Z3 & ";" & Z3 & "<=" & T & ");AND(" & T & "<" & Z4 & ";" & Z4 & "<" & Z3 & "))"
Dim currentLine As Range
Set currentLine = Range(Cells(dline, data.Column), Cells(dline, data.Column).Offset(0, width))
currentLine.Select ' <- neccessary change solving the problem
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)
这篇关于Excel更改条件格式公式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
