Testing for Interior.ColorIndex

[Guest]

Hello All

I have a snippet of code that I've been using to test for the color format of a cell in a particular range and then change the color format of cells in a parallel range based upon the outcome of the test

Unfortunately, when I used this code snippet in a recent script my test isn't working

Here's the snippet and the result I'm getting

For Each c In Worksheets("Factors").Range("RANK"
If c.Interior.ColorIndex = 37 Then Worksheets("Factors").Range("FNUM").Cells(counter).Interior.ColorIndex = 40 'salmo
If c.Interior.ColorIndex = 4 Then Worksheets("Factors").Range("FNUM").Cells(counter).Interior.ColorIndex = 40 'salmo
counter = counter +
Next

I have a watch on the c.Interior.ColorIndex and when it hits the first cell in "RANK" the value becomes -4142 (variant/long). Typically the cell format should be either 37, 4, or no format.

Any ideas where the -4142 is coming from

Thanks!

 

[Frank Kabel]

Hi
this is the default value for 'no color'

 

[Guest]

Thanks Frank.

Then this narrows my question down to why the test results in "no color" when I can see that the cell is in fact colored? I've used c.select and F8 to watch the script cycle through each cell so I know it's selecting the right one

Thanks, in advance.

 

[Tom Ogilvy]

so what is the value displayed by

c.interior.colorindex

when c refers to the "right one" ( a colored cell).

--
Regards,
Tom Ogilvy

Thanks Frank.

Then this narrows my question down to why the test results in "no color"

when I can see that the cell is in fact colored? I've used c.select and F8
to watch the script cycle through each cell so I know it's selecting the
right one.

 

[Guest]

Well, c seems to be referring to the right cell (since I can see it selecting each cell, one after the other, in the range). I expect the result to test as either 37 or 4 (if it's colored... then it paints the corresponding cell in another range with 40... which is salmon). However, the only result I get back is the -4142 (which Frank noted is "no color")

The code seems simple enough that it should work, but for some reason it isn't and I was hoping I'd missed something obvious

Here's another piece that does work. It uses two ranges, FVAL (the range to receive the coloring) and ORIG (a range containing -1's, 1's and 0's. The result should change the color formatting for FVAL based on the value in the cell and the value of the corresponding cell in ORIG. This one works

Public Sub ColorizeFVAL(

counter =

For Each c In Range("FVAL").Cell
'colorize background based on ran
If c > 0.9 The
With c.Fon
.FontStyle = "Bold
End Wit
With c.Interio
.ColorIndex =
.Pattern = xlSoli
End Wit
End I
If c <= 0.9 And c > 0.8 The
With c.Fon
.FontStyle = "Bold
End Wit
With c.Interio
.ColorIndex = 3
.Pattern = xlSoli
End Wit
End I
If c <= 0.8 And c > 0.7 The
With c.Fon
.FontStyle = "Bold
End Wit
With c.Interio
.ColorIndex =
.Pattern = xlSoli
End Wit
End I
'colorize negative values with re
If Range("ORIG").Cells(counter) < 0 The
With c.Fon
.FontStyle = "Bold
.ColorIndex =
End Wit
End I
counter = counter +
Next

End Su

.... meanwhile, back at the ranch I've used another method to work around this problem. I'd still be interested if anyone has any ideas why the test isn't working

Thanks...

 

[Frank Kabel]

Hi
how is the cell colored?. With a conditional format?. If yes: The
conditional format color is NOT shown by the colorindex property

 

[Simon Lloyd]

Try Chip pearsons site....this will help!

http://www.cpearson.com/excel/colors.htm

Simo

 

[Tom Ogilvy]

If your anticipating that the answer will be yes, that it is produced by
conditional formatting, then right site, wrong page:

http://www.cpearson.com/excel/CFColors.htm