Conditional Formatting and Interior.ColorIndex
 

Windows XP Pro SP2
Excel 2002 SP3

Seems that cells that are conditionally formatted don't return an expected
Interior.ColorIndex value.

I am using this bit of code:

Set rng1 = Range("B20:P20")
For Each c1 In rng1.Cells
v1 = c1.Value
If v1 < "" Then
Set rng2 = Range(c1.Offset(3, 0), c1.Offset(32770, 0))
rng2.FormatConditions.Add xlCellValue, xlEqual, v1
rng2.FormatConditions(1).Interior.ColorIndex = 4
rng2.FormatConditions.Add xlCellValue, xlNotEqual, v1
rng2.FormatConditions(2).Interior.ColorIndex = 46
End If
Next c1

This works fine and colors the interiors as I would expect. However, if I
add this after the last line above

MsgBox Range("G23").Interior.ColorIndex 'G23 is colored red (46)

the message box says -4142.

What's up with that?

Any help greatly appreciated.

-gk-

GO TAR HEELS ! ! !

Conditional Formatting and Interior.ColorIndex
 

-4142 is the value assigned to at least two Excel constants...
xlNone and xlColorIndexNone

Colors provided by Conditional formatting operate by their own rules.
They cannot be accessed using "colorindex".
See the Chip Pearson discussion here...
http://www.cpearson.com/excel/CFColors.htm
Get plenty of sleep first and pack a lunch. <g

You may have some interest in the free Excel add-in "Determine Colors".
It can be downloaded from the bottom of the Products page at my website.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)





"Farrar Tweety"
wrote in message
Windows XP Pro SP2
Excel 2002 SP3

Seems that cells that are conditionally formatted don't return an expected
Interior.ColorIndex value.
I am using this bit of code:

Set rng1 = Range("B20:P20")
For Each c1 In rng1.Cells
v1 = c1.Value
If v1 < "" Then
Set rng2 = Range(c1.Offset(3, 0), c1.Offset(32770, 0))
rng2.FormatConditions.Add xlCellValue, xlEqual, v1
rng2.FormatConditions(1).Interior.ColorIndex = 4
rng2.FormatConditions.Add xlCellValue, xlNotEqual, v1
rng2.FormatConditions(2).Interior.ColorIndex = 46
End If
Next c1

This works fine and colors the interiors as I would expect. However, if I
add this after the last line above

MsgBox Range("G23").Interior.ColorIndex 'G23 is colored red (46)

the message box says -4142.

What's up with that?

Any help greatly appreciated.

-gk-

GO TAR HEELS ! ! !

Conditional Formatting and Interior.ColorIndex
 

Thank you Jim, and sorry for the double post. Chip's site has long been
bookmarked, so I suppose I probably should have looked there first. <g

Looks like I'll be bookmarking your site as well. Thanks!!

-gk-


"Jim Cone" wrote in message
...

-4142 is the value assigned to at least two Excel constants...
xlNone and xlColorIndexNone

Colors provided by Conditional formatting operate by their own rules.
They cannot be accessed using "colorindex".
See the Chip Pearson discussion here...
http://www.cpearson.com/excel/CFColors.htm
Get plenty of sleep first and pack a lunch. <g

You may have some interest in the free Excel add-in "Determine Colors".
It can be downloaded from the bottom of the Products page at my website.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)





"Farrar Tweety"
wrote in message
Windows XP Pro SP2
Excel 2002 SP3

Seems that cells that are conditionally formatted don't return an expected
Interior.ColorIndex value.
I am using this bit of code:

Set rng1 = Range("B20:P20")
For Each c1 In rng1.Cells
v1 = c1.Value
If v1 < "" Then
Set rng2 = Range(c1.Offset(3, 0), c1.Offset(32770, 0))
rng2.FormatConditions.Add xlCellValue, xlEqual, v1
rng2.FormatConditions(1).Interior.ColorIndex = 4
rng2.FormatConditions.Add xlCellValue, xlNotEqual, v1
rng2.FormatConditions(2).Interior.ColorIndex = 46
End If
Next c1

This works fine and colors the interiors as I would expect. However, if I
add this after the last line above

MsgBox Range("G23").Interior.ColorIndex 'G23 is colored red (46)

the message box says -4142.

What's up with that?

Any help greatly appreciated.

-gk-

GO TAR HEELS ! ! !

Conditional Formatting and Interior.ColorIndex
 

If you use this syntax it will produce the ColorIndex number you are looking
for.

MsgBox Range("G23").FormatConditions(2).Interior.ColorInd ex

The problem is that you cannot use a generic FormatConditions, it must be
specific as (1), (2) or (3).



"Farrar Tweety" wrote:

Windows XP Pro SP2
Excel 2002 SP3

Seems that cells that are conditionally formatted don't return an expected
Interior.ColorIndex value.

I am using this bit of code:

Set rng1 = Range("B20:P20")
For Each c1 In rng1.Cells
v1 = c1.Value
If v1 < "" Then
Set rng2 = Range(c1.Offset(3, 0), c1.Offset(32770, 0))
rng2.FormatConditions.Add xlCellValue, xlEqual, v1
rng2.FormatConditions(1).Interior.ColorIndex = 4
rng2.FormatConditions.Add xlCellValue, xlNotEqual, v1
rng2.FormatConditions(2).Interior.ColorIndex = 46
End If
Next c1

This works fine and colors the interiors as I would expect. However, if I
add this after the last line above

MsgBox Range("G23").Interior.ColorIndex 'G23 is colored red (46)

the message box says -4142.

What's up with that?

Any help greatly appreciated.

-gk-

GO TAR HEELS ! ! !