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

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

Erase ".Selection". By the way ColorIndex 1 is black, ColorIndex 3 is
red.

Hth,
Merjet

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.

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

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