Excel VBA Conditional Formatting not executing

3
votes

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.

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.

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.

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

1
excelvbaformattingconditional
You are aware that "<" means the same thing as "less than", right? Just checking! – Jean-François Corbett
Yes! That was a typo in my question... – BonnieSF

1 Answers

6
votes

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

  1. Find some blank cell off to the side and enter the number 0.
  2. Copy that cell (Ctrl-C).
  3. 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.