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!

Hi
this is the default value for 'no color'

--
Regards
Frank Kabel
Frankfurt, Germany


JeffBo wrote:
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 'salmon If c.Interior.ColorIndex = 4 Then

Worksheets("Factors").Range("FNUM").Cells(counter) .Interior.ColorIndex
= 40 'salmon counter = counter + 1
Next c

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!

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.

so what is the value displayed by

c.interior.colorindex

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

--
Regards,
Tom Ogilvy

"Jeffbo" wrote in message
...
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.

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...

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

--
Regards
Frank Kabel
Frankfurt, Germany


JeffBo wrote:
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 = 1

For Each c In Range("FVAL").Cells
'colorize background based on rank
If c 0.9 Then
With c.Font
.FontStyle = "Bold"
End With
With c.Interior
.ColorIndex = 4
.Pattern = xlSolid
End With
End If
If c <= 0.9 And c 0.8 Then
With c.Font
.FontStyle = "Bold"
End With
With c.Interior
.ColorIndex = 33
.Pattern = xlSolid
End With
End If
If c <= 0.8 And c 0.7 Then
With c.Font
.FontStyle = "Bold"
End With
With c.Interior
.ColorIndex = 6
.Pattern = xlSolid
End With
End If
'colorize negative values with red
If Range("ORIG").Cells(counter) < 0 Then
With c.Font
.FontStyle = "Bold"
.ColorIndex = 3
End With
End If
counter = counter + 1
Next c

End Sub

... 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...

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

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

Simo

--
Message posted from http://www.ExcelForum.com

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

--
Regards,
Tom Ogilvy

"Simon Lloyd " wrote in message
...
Try Chip pearsons site....this will help!

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

Simon


---
Message posted from http://www.ExcelForum.com/