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

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 <(E-Mail Removed)>,
"TJ" <(E-Mail Removed)> 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

Thanks guys
"TJ" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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
>

That's absolutely true only for the default color palette. Palette
colors can be changed.

In article <(E-Mail Removed)>,
"merjet" <(E-Mail Removed)> wrote:

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