4

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.

Jean-François Corbett
  • 37,420
  • 30
  • 139
  • 188
BonnieSF
  • 43
  • 3

1 Answers1

6

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.

Rachel Hettinger
  • 7,927
  • 2
  • 21
  • 31
  • Hi Rachel, your idea worked! Thank you! Now I need to figure out how to incorporate this into my VBA code. Or, maybe there is another way to make excel see these as numeric values. Any ideas? I'll hunt around for that particular thing - I can't be the only one with this problem. – BonnieSF Dec 07 '11 at 18:47
  • How exactly are you getting the data into Excel? For some reason the numerical values are being interpreted as text. – Rachel Hettinger Dec 07 '11 at 20:34
  • I'm using the MS Access CopyFromRecord method. From what I can tell by googling, it strips out formatting from Excel. – BonnieSF Dec 07 '11 at 23:14
  • I got it to work! In the section of code that was assigning the conditional formatting, I added a line to set the cell value to .value + 0; then it WORKED!!! – BonnieSF Dec 07 '11 at 23:37
  • Thank you Rachel for your clever idea! – BonnieSF Dec 07 '11 at 23:38