Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Counting Colours
Hi
I am trying to write a short piece of code that loops through a number of cells in a given row and increments a counter depending on what colour the interior of the cell is. I am using the following code For vC = 10 To 100 With Worksheets("Closed Issues").Cells(vC, 6) Select Case (.Selection.Interior.ColorIndex) Case 6 'Yellow vYellow = vYellow + 1 Case 1 'Red vRed = vRed + 1 End Select End With Next but keep getting error 438 'Object doe not support this method or property'. Can anyone see a reason why this is happening or suggest a solution? Thanks TJ |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Counting Colours
Remove the selection part in your Select Case:
For vC = 10 To 100 With Worksheets("Closed Issues").Cells(vC, 6) Select Case (.Interior.ColorIndex) Case 6 'Yellow vYellow = vYellow + 1 Case 1 'Red vRed = vRed + 1 End Select End With Next -- Regards, Tom Ogilvy "TJ" wrote: Hi I am trying to write a short piece of code that loops through a number of cells in a given row and increments a counter depending on what colour the interior of the cell is. I am using the following code For vC = 10 To 100 With Worksheets("Closed Issues").Cells(vC, 6) Select Case (.Selection.Interior.ColorIndex) Case 6 'Yellow vYellow = vYellow + 1 Case 1 'Red vRed = vRed + 1 End Select End With Next but keep getting error 438 'Object doe not support this method or property'. Can anyone see a reason why this is happening or suggest a solution? Thanks TJ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Counting Colours
The Cells() object doesn't have a .Selection Property.
Try: For vC = 10 To 100 With Worksheets("Closed Issues").Cells(vC, 6) Select Case (.Interior.ColorIndex) Case 6 'Yellow vYellow = vYellow + 1 Case 1 'Red vRed = vRed + 1 End Select End With Next But the With doesn't really make anything more efficient since it's inside the loop and is executed every time. Better: With Worksheets("Closed Issues") For vC = 10 To 100 Select Case (.Cells(vC, 6).Interior.ColorIndex) Case 6 'Yellow vYellow = vYellow + 1 Case 1 'Red vRed = vRed + 1 End Select Next End With In article , "TJ" wrote: Hi I am trying to write a short piece of code that loops through a number of cells in a given row and increments a counter depending on what colour the interior of the cell is. I am using the following code For vC = 10 To 100 With Worksheets("Closed Issues").Cells(vC, 6) Select Case (.Selection.Interior.ColorIndex) Case 6 'Yellow vYellow = vYellow + 1 Case 1 'Red vRed = vRed + 1 End Select End With Next but keep getting error 438 'Object doe not support this method or property'. Can anyone see a reason why this is happening or suggest a solution? Thanks TJ |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Counting Colours
Erase ".Selection". By the way ColorIndex 1 is black, ColorIndex 3 is
red. Hth, Merjet |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Counting Colours
Thanks guys
"TJ" wrote in message ... Hi I am trying to write a short piece of code that loops through a number of cells in a given row and increments a counter depending on what colour the interior of the cell is. I am using the following code For vC = 10 To 100 With Worksheets("Closed Issues").Cells(vC, 6) Select Case (.Selection.Interior.ColorIndex) Case 6 'Yellow vYellow = vYellow + 1 Case 1 'Red vRed = vRed + 1 End Select End With Next but keep getting error 438 'Object doe not support this method or property'. Can anyone see a reason why this is happening or suggest a solution? Thanks TJ |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Counting Colours
That's absolutely true only for the default color palette. Palette
colors can be changed. In article . com, "merjet" wrote: Erase ".Selection". By the way ColorIndex 1 is black, ColorIndex 3 is red. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Counting Colours in Excel | New Users to Excel | |||
Counting Cells with specific Colours | Excel Discussion (Misc queries) | |||
Counting cell colours | Excel Worksheet Functions | |||
Counting colours | Excel Worksheet Functions | |||
Counting colours | Excel Discussion (Misc queries) |