title: Macro produces negative font.colorindex values
body: <p>Clues to possible reason at the end of this initial discussion.....</p>
  
  <p>A simple VBA macro code line like:</p>
  
  <pre><code>FontCol = Cells(K, AmntCol).Font.ColorIndex
  </code></pre>
  
  <p>reports, with the cursor hovering over "FontCol", the value "1"
  - which is the index value for black.</p>
  
  <p>yet when this value is "published" to the relevant (active) Excel spreadsheet with:</p>
  
  <pre><code>Cells(RowRef, ColRef) = FontCol
  </code></pre>
  
  <p>the <strong>Cell addressed as above shows -5 instead of 1</strong> (as one example of the problem)</p>
  
  <p>"1" is what you expect to see for black, and I cannot even work out what "-5" relates to
  let alone why it is also not what seems to be in play in the VBA code.
  - and if I write "-5" into some code logic, no useful result would come from it, which confirms that the real underlying value is 1 and not -5.</p>
  
  <p>It seems that no amount of formatting of the (landing) cell or changing the "Dimension as statement" for FontCol, to "integer" or "string" or "variant" changes what is published to the cell. </p>
  
  <p>I've not been able to source any information on this issue. The closest was something about long numbers wrapping around shorter numbers in the index space and a residual driving things...whatever that is supposed to mean. </p>
  
  <p>I really would to resolve this as I was intending to use ColorIndex values as a way of editing cell values safely.</p>
  
  <p>Any wisdom would be greatly appreciated.</p>
  
  <p>Additional to above - in response.</p>
  
  <p>Changing Dim of variable/s from "integer" to "long" makes no difference.</p>
  
  <p>In preparing to provide sample code as below I have determined part of the problem - its seems its speed related. But first the code.</p>
  
  <p>''''
      Sub FontMacroIssue()</p>
  
  <pre><code>Dim I As Long
  Dim FontCol As Long
  
  For I = 36 To 51
      FontCol = Cells(I, 10).Font.ColorIndex
      If FontCol = 3 Then
          Cells(I, 18) = "Red"
          Cells(I, 19) = FontCol
      Else
          Cells(I, 18) = "Not Red"
          Cells(I, 19) = FontCol
      End If
  Next I
  
  End Sub
  ''''
  </code></pre>
  
  <p>The above is being run on a Windows 10 64 bit machine (that dual boots into Linux)...this should not be relevant.</p>
  
  <p>If the above is run as is then the output is mostly/all "not red" and the string of FontCol values print out as, </p>
  
  <p>1 -5 -5 -5 -5 -7 -7 -7 -7 -7 -7 -7 -7 -7 -5 -7 - and if it is slowed, as
  1  3  3  3  3  1  1  1  1  1  1  1  1  1  3  1</p>
  
  <p>By slowed, I mean running the macro in debug mode with a break point at "Next I" or in a similar place, and then keying the F5 button (say) twice a second. The output for the full speed case is mostly/all "not red" but is the correct reporting of "red" and "not red" when slowed for the font being assessed.</p>
  
  <p>So my question/s now are more, "is this behaviour normal", "how can I best slow things down to get a correct result" OR "what code is more effective" ?
  edit...</p>
  
  <p>I am inclined to agree with Fun Thomas as even 2 cycles/sec brings up the problem.
  I do not have an answer but some comments that might help.
  1.  My hard drive is a 2 TB SSD
  2.  The spreadsheet concerned has over 46000 lines.
  3.  It surely is more than coincidence that:
      8 -7 = 1  with both 1 and -7 being reported for black font, and
      8 -5 = 3  with both 3 and -5 being reported for red font.
  Thanks</p>