Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 ! ! ! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional Formatting and Interior.ColorIndex
Hi,
Excel will retirn -4142 if the interior has no colour and you can't get the index of a cell if it is coloured by conditional formatting. The most common approach is to test for the condition that would cause the cell to change colour. Mike "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 ! ! ! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional Formatting and Interior.ColorIndex
Mike,
I'm using a colorIndex function for a search routine to identify when a cell or range of cells is NOT colored. I'd like to do a search to locate a colored cell, but since I never know what the color will be, I thought having it look for its antithesis might be better. Got any thoughts/ideas? Thanks. "Mike H" wrote: Hi, Excel will retirn -4142 if the interior has no colour and you can't get the index of a cell if it is coloured by conditional formatting. The most common approach is to test for the condition that would cause the cell to change colour. Mike "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 ! ! ! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional Formatting and Interior.ColorIndex | Excel Programming | |||
Use of Interior.ColorIndex | Excel Programming | |||
interior.colorindex used with conditional formatting | Excel Worksheet Functions | |||
conditional formatting and interior.colorindex | Excel Programming | |||
Testing for Interior.ColorIndex | Excel Programming |