Excel VBA条件格式未执行 [英] Excel VBA Conditional Formatting not executing

问题描述

这很奇怪.我正在从MS Access 2003 VBA创建一个Excel 2003电子表格，并且编码包括条件格式设置:如果单元格值大于[a value]，则单元格颜色为红色；如果小于[a value]，则单元格颜色为绿色.

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.

即使在生成电子表格时成功创建了CF公式，无论值如何，所有颜色均为绿色.如果我手动将原始值输入到单元格中，它将触发CF，然后它会正确显示，否则，它会坐在所有相同(错误)的颜色上.

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.

我尝试执行 application.calculate ， CalculateFull ， CalculateFullRebuild ；但这无济于事.我已经尝试过 worksheet.calculate .不.我尝试将电子表格的所有内容处理为新的电子表格.不.我尝试为工作表中的所有单元格指定数字格式.不.

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.

好像要重新计算，但我无法理解.

It seems as it if wants to recalculate, but I can't get that going.

我努力地用Google搜索，但是找不到这样的东西，这让我觉得我缺少基本知识.

I have googled this hard, but can't find anything like this, which makes me think I'm missing something elementary.

推荐答案

听起来您的数据是数字的，但Excel却将其视为文本.这是解决问题的一种方法:

It sounds like your data is numeric, but Excel sees it as text. Here's a way to resolve the issue:

1. 在侧面找到一些空白单元格，然后输入数字0.
2. 复制该单元格(Ctrl-C).
3. 选择有问题的数据单元，并使用以下设置进行选择性粘贴":从粘贴"部分选择值"，从操作"部分选择添加"，单击确定".

这是通过向每个单元格添加零来实现的，该单元格不会更改其值，但会强制Excel将其视为数字.

This works by adding zero to each cell which won't change their value but will force Excel to see them as numeric.

这篇关于Excel VBA条件格式未执行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！