This is odd. I'm creating an Excel 2003 spreadsheet from MS Access 2003 VBA, and the coding includes conditional formatting: if the cell value is greater than [a value] it's colored red if it's less than [a value], it's colored green.
这很奇怪。我正在从MS Access 2003 VBA创建Excel 2003电子表格，编码包括条件格式：如果单元格值大于[值]，如果它小于[值]则为红色，则为绿色。
Even though the CF formulas are created successfully when the spreadsheet is generated, all the colors are green, regardless of the value. If I manually type the original value into a cell, it will trigger then CF and it appears correctly, but otherwise it just sits there all the same (wrong) color.
即使在生成电子表格时成功创建了CF公式，但无论值如何，所有颜色都是绿色。如果我手动将原始值键入单元格，它将触发CF然后正确显示，但否则它只是位于那里所有相同（错误）的颜色。
I have tried to execute application.calculate, CalculateFull, CalculateFullRebuild; but it does not help. I have tried worksheet.calculate. Nope. I tried coping all the contents of the spreadsheet to a new spreadsheet. Nope. I have tried specifying number format for all the cells in the worksheet. Nope.
我试图执行application.calculate，CalculateFull，CalculateFullRebuild;但它没有帮助。我试过workheet.calculate。不。我尝试将电子表格的所有内容复制到新的电子表格中。不。我已经尝试为工作表中的所有单元格指定数字格式。不。
It seems as it if wants to recalculate, but I can't get that going.
似乎它想要重新计算，但我无法做到这一点。
I have googled this hard, but can't find anything like this, which makes me think I'm missing something elementary.
我用Google搜索了这个，但找不到这样的东西，这让我觉得我错过了一些基本的东西。
1 个解决方案
#1
6
It sounds like your data is numeric, but Excel sees it as text. Here's a way to resolve the issue:
听起来您的数据是数字，但Excel将其视为文本。这是解决问题的方法：
- Find some blank cell off to the side and enter the number 0.
- 找到一些空白单元格并输入数字0。
- Copy that cell (Ctrl-C).
- 复制该单元格（Ctrl-C）。
- Select the problematic data cells and do Paste Special with the following settings: chose Values from the "Paste" section and Add from the "Operation" section, click OK.
- 选择有问题的数据单元格并使用以下设置执行“选择性粘贴”：从“粘贴”部分选择“值”，然后从“操作”部分选择“添加”，单击“确定”。
This works by adding zero to each cell which won't change their value but will force Excel to see them as numeric.
这可以通过向每个单元格添加零来实现，这些单元格不会更改其值，但会强制Excel将它们视为数字。
#1
6
It sounds like your data is numeric, but Excel sees it as text. Here's a way to resolve the issue:
听起来您的数据是数字，但Excel将其视为文本。这是解决问题的方法：
- Find some blank cell off to the side and enter the number 0.
- 找到一些空白单元格并输入数字0。
- Copy that cell (Ctrl-C).
- 复制该单元格（Ctrl-C）。
- Select the problematic data cells and do Paste Special with the following settings: chose Values from the "Paste" section and Add from the "Operation" section, click OK.
- 选择有问题的数据单元格并使用以下设置执行“选择性粘贴”：从“粘贴”部分选择“值”，然后从“操作”部分选择“添加”，单击“确定”。
This works by adding zero to each cell which won't change their value but will force Excel to see them as numeric.
这可以通过向每个单元格添加零来实现，这些单元格不会更改其值，但会强制Excel将它们视为数字。