Hi,

I have an excel file that has 17000 rows and 33 cols.
Some of the cells in the file are highlighted yellow.
I am trying to mark (with an X) in col 34 at the end what rows contains the
highlighted cell

My code below:

Dim rngCells As Range
Dim intRows As Long
Dim intFields As Long
Dim iRow As Long
Dim iField As Long

Set rngCells = Application.ActiveWorkbook.ActiveSheet.UsedRange

intRows = rngCells.Rows.Count
intFields = rngCells.Columns.Count

For iRow = 1 To intRows Step 2
For iField = 1 To intFields
If Cells(iRow, iField).ColorIndex.Value = 6 Then
With Cells(iRow, 34)
.Value = "X"
End With
End If
Next iField
Next iRow

Set rngCells = Nothing

MsgBox "Yellow colors flagged"

The code gives me an error on the first If saying "Object doesnt support
this property or method"
Can anyone help? or is my code completely wrong?

Thanks in advance
David

David,

The correct syntax for this line

If Cells(iRow, iField).ColorIndex.Value = 6 Then

is

If Cells(iRow, iField).Interior.ColorIndex = 6 Then

Mike

"David Kennedy" wrote:

> Hi,
>
> I have an excel file that has 17000 rows and 33 cols.
> Some of the cells in the file are highlighted yellow.
> I am trying to mark (with an X) in col 34 at the end what rows contains the
> highlighted cell
>
> My code below:
>
> Dim rngCells As Range
> Dim intRows As Long
> Dim intFields As Long
> Dim iRow As Long
> Dim iField As Long
>
> Set rngCells = Application.ActiveWorkbook.ActiveSheet.UsedRange
>
> intRows = rngCells.Rows.Count
> intFields = rngCells.Columns.Count
>
> For iRow = 1 To intRows Step 2
> For iField = 1 To intFields
> If Cells(iRow, iField).ColorIndex.Value = 6 Then
> With Cells(iRow, 34)
> .Value = "X"
> End With
> End If
> Next iField
> Next iRow
>
> Set rngCells = Nothing
>
> MsgBox "Yellow colors flagged"
>
> The code gives me an error on the first If saying "Object doesnt support
> this property or method"
> Can anyone help? or is my code completely wrong?
>
> Thanks in advance
> David
>
>
>

If Cells(iRow, iField).Font.ColorIndex = 6 Then
or
If Cells(iRow, iField).Interior.ColorIndex = 6 Then

(Font color or fill color??)

David Kennedy wrote:
>
> Hi,
>
> I have an excel file that has 17000 rows and 33 cols.
> Some of the cells in the file are highlighted yellow.
> I am trying to mark (with an X) in col 34 at the end what rows contains the
> highlighted cell
>
> My code below:
>
> Dim rngCells As Range
> Dim intRows As Long
> Dim intFields As Long
> Dim iRow As Long
> Dim iField As Long
>
> Set rngCells = Application.ActiveWorkbook.ActiveSheet.UsedRange
>
> intRows = rngCells.Rows.Count
> intFields = rngCells.Columns.Count
>
> For iRow = 1 To intRows Step 2
> For iField = 1 To intFields
> If Cells(iRow, iField).ColorIndex.Value = 6 Then
> With Cells(iRow, 34)
> .Value = "X"
> End With
> End If
> Next iField
> Next iRow
>
> Set rngCells = Nothing
>
> MsgBox "Yellow colors flagged"
>
> The code gives me an error on the first If saying "Object doesnt support
> this property or method"
> Can anyone help? or is my code completely wrong?
>
> Thanks in advance
> David

--

Dave Peterson

Try filling an odd numbered row because that's what your testing

"David Kennedy" wrote:

> Thanks for the swift reply,
>
> Fill color,
>
> for testing I filled in yellow on row2 col1
> I applied your fix but the code doesnt seem to recognise the yellow filled
> field
>
>
>
>
>
> "Dave Peterson" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > If Cells(iRow, iField).Font.ColorIndex = 6 Then
> > or
> > If Cells(iRow, iField).Interior.ColorIndex = 6 Then
> >
> > (Font color or fill color??)
> >
> > David Kennedy wrote:
> >>
> >> Hi,
> >>
> >> I have an excel file that has 17000 rows and 33 cols.
> >> Some of the cells in the file are highlighted yellow.
> >> I am trying to mark (with an X) in col 34 at the end what rows contains
> >> the
> >> highlighted cell
> >>
> >> My code below:
> >>
> >> Dim rngCells As Range
> >> Dim intRows As Long
> >> Dim intFields As Long
> >> Dim iRow As Long
> >> Dim iField As Long
> >>
> >> Set rngCells = Application.ActiveWorkbook.ActiveSheet.UsedRange
> >>
> >> intRows = rngCells.Rows.Count
> >> intFields = rngCells.Columns.Count
> >>
> >> For iRow = 1 To intRows Step 2
> >> For iField = 1 To intFields
> >> If Cells(iRow, iField).ColorIndex.Value = 6 Then
> >> With Cells(iRow, 34)
> >> .Value = "X"
> >> End With
> >> End If
> >> Next iField
> >> Next iRow
> >>
> >> Set rngCells = Nothing
> >>
> >> MsgBox "Yellow colors flagged"
> >>
> >> The code gives me an error on the first If saying "Object doesnt support
> >> this property or method"
> >> Can anyone help? or is my code completely wrong?
> >>
> >> Thanks in advance
> >> David
> >
> > --
> >
> > Dave Peterson
>
>
>

Thanks for the swift reply,

Fill color,

for testing I filled in yellow on row2 col1
I applied your fix but the code doesnt seem to recognise the yellow filled
field





"Dave Peterson" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> If Cells(iRow, iField).Font.ColorIndex = 6 Then
> or
> If Cells(iRow, iField).Interior.ColorIndex = 6 Then
>
> (Font color or fill color??)
>
> David Kennedy wrote:
>>
>> Hi,
>>
>> I have an excel file that has 17000 rows and 33 cols.
>> Some of the cells in the file are highlighted yellow.
>> I am trying to mark (with an X) in col 34 at the end what rows contains
>> the
>> highlighted cell
>>
>> My code below:
>>
>> Dim rngCells As Range
>> Dim intRows As Long
>> Dim intFields As Long
>> Dim iRow As Long
>> Dim iField As Long
>>
>> Set rngCells = Application.ActiveWorkbook.ActiveSheet.UsedRange
>>
>> intRows = rngCells.Rows.Count
>> intFields = rngCells.Columns.Count
>>
>> For iRow = 1 To intRows Step 2
>> For iField = 1 To intFields
>> If Cells(iRow, iField).ColorIndex.Value = 6 Then
>> With Cells(iRow, 34)
>> .Value = "X"
>> End With
>> End If
>> Next iField
>> Next iRow
>>
>> Set rngCells = Nothing
>>
>> MsgBox "Yellow colors flagged"
>>
>> The code gives me an error on the first If saying "Object doesnt support
>> this property or method"
>> Can anyone help? or is my code completely wrong?
>>
>> Thanks in advance
>> David
>
> --
>
> Dave Peterson

A highly simplified version you can modify

Sub colorif()
lr = ActiveSheet.UsedRange.Rows.Count
'MsgBox lr
For i = 1 To lr
lc = Cells(i, Columns.Count).End(xlToLeft).Column
'MsgBox lc
For j = 1 To lc
If Cells(i, j).Interior.ColorIndex = 6 Then Cells(i, 34) = "x"
'MsgBox i
Next j
Next i
End Sub
--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(E-Mail Removed)
"David Kennedy" <(E-Mail Removed)> wrote in message
news:%23RZt49$(E-Mail Removed)...
> Hi,
>
> I have an excel file that has 17000 rows and 33 cols.
> Some of the cells in the file are highlighted yellow.
> I am trying to mark (with an X) in col 34 at the end what rows contains
> the highlighted cell
>
> My code below:
>
> Dim rngCells As Range
> Dim intRows As Long
> Dim intFields As Long
> Dim iRow As Long
> Dim iField As Long
>
> Set rngCells = Application.ActiveWorkbook.ActiveSheet.UsedRange
>
> intRows = rngCells.Rows.Count
> intFields = rngCells.Columns.Count
>
> For iRow = 1 To intRows Step 2
> For iField = 1 To intFields
> If Cells(iRow, iField).ColorIndex.Value = 6 Then
> With Cells(iRow, 34)
> .Value = "X"
> End With
> End If
> Next iField
> Next iRow
>
> Set rngCells = Nothing
>
> MsgBox "Yellow colors flagged"
>
> The code gives me an error on the first If saying "Object doesnt support
> this property or method"
> Can anyone help? or is my code completely wrong?
>
> Thanks in advance
> David
>
>

Even simpler but may take awhile
Sub colorif2()
For Each c In ActiveSheet.UsedRange
If c.Interior.ColorIndex = 6 Then Cells(c.Row, 34) = "X"
Next c
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(E-Mail Removed)
"Don Guillett" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>A highly simplified version you can modify
>
> Sub colorif()
> lr = ActiveSheet.UsedRange.Rows.Count
> 'MsgBox lr
> For i = 1 To lr
> lc = Cells(i, Columns.Count).End(xlToLeft).Column
> 'MsgBox lc
> For j = 1 To lc
> If Cells(i, j).Interior.ColorIndex = 6 Then Cells(i, 34) = "x"
> 'MsgBox i
> Next j
> Next i
> End Sub
> --
> Don Guillett
> Microsoft MVP Excel
> SalesAid Software
> (E-Mail Removed)
> "David Kennedy" <(E-Mail Removed)> wrote in message
> news:%23RZt49$(E-Mail Removed)...
>> Hi,
>>
>> I have an excel file that has 17000 rows and 33 cols.
>> Some of the cells in the file are highlighted yellow.
>> I am trying to mark (with an X) in col 34 at the end what rows contains
>> the highlighted cell
>>
>> My code below:
>>
>> Dim rngCells As Range
>> Dim intRows As Long
>> Dim intFields As Long
>> Dim iRow As Long
>> Dim iField As Long
>>
>> Set rngCells = Application.ActiveWorkbook.ActiveSheet.UsedRange
>>
>> intRows = rngCells.Rows.Count
>> intFields = rngCells.Columns.Count
>>
>> For iRow = 1 To intRows Step 2
>> For iField = 1 To intFields
>> If Cells(iRow, iField).ColorIndex.Value = 6 Then
>> With Cells(iRow, 34)
>> .Value = "X"
>> End With
>> End If
>> Next iField
>> Next iRow
>>
>> Set rngCells = Nothing
>>
>> MsgBox "Yellow colors flagged"
>>
>> The code gives me an error on the first If saying "Object doesnt support
>> this property or method"
>> Can anyone help? or is my code completely wrong?
>>
>> Thanks in advance
>> David
>>
>>
>

Don,

The OP is only checking odd rows so this makes your code go nearly twice as
fast

For Each c In ActiveSheet.UsedRange
If c.Interior.ColorIndex = 6 And c.Row Mod 2 = 1 _
Then Cells(c.Row, 34) = "X"
Next c


Mike

"Don Guillett" wrote:

> Even simpler but may take awhile
> Sub colorif2()
> For Each c In ActiveSheet.UsedRange
> If c.Interior.ColorIndex = 6 Then Cells(c.Row, 34) = "X"
> Next c
> End Sub
>
> --
> Don Guillett
> Microsoft MVP Excel
> SalesAid Software
> (E-Mail Removed)
> "Don Guillett" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> >A highly simplified version you can modify
> >
> > Sub colorif()
> > lr = ActiveSheet.UsedRange.Rows.Count
> > 'MsgBox lr
> > For i = 1 To lr
> > lc = Cells(i, Columns.Count).End(xlToLeft).Column
> > 'MsgBox lc
> > For j = 1 To lc
> > If Cells(i, j).Interior.ColorIndex = 6 Then Cells(i, 34) = "x"
> > 'MsgBox i
> > Next j
> > Next i
> > End Sub
> > --
> > Don Guillett
> > Microsoft MVP Excel
> > SalesAid Software
> > (E-Mail Removed)
> > "David Kennedy" <(E-Mail Removed)> wrote in message
> > news:%23RZt49$(E-Mail Removed)...
> >> Hi,
> >>
> >> I have an excel file that has 17000 rows and 33 cols.
> >> Some of the cells in the file are highlighted yellow.
> >> I am trying to mark (with an X) in col 34 at the end what rows contains
> >> the highlighted cell
> >>
> >> My code below:
> >>
> >> Dim rngCells As Range
> >> Dim intRows As Long
> >> Dim intFields As Long
> >> Dim iRow As Long
> >> Dim iField As Long
> >>
> >> Set rngCells = Application.ActiveWorkbook.ActiveSheet.UsedRange
> >>
> >> intRows = rngCells.Rows.Count
> >> intFields = rngCells.Columns.Count
> >>
> >> For iRow = 1 To intRows Step 2
> >> For iField = 1 To intFields
> >> If Cells(iRow, iField).ColorIndex.Value = 6 Then
> >> With Cells(iRow, 34)
> >> .Value = "X"
> >> End With
> >> End If
> >> Next iField
> >> Next iRow
> >>
> >> Set rngCells = Nothing
> >>
> >> MsgBox "Yellow colors flagged"
> >>
> >> The code gives me an error on the first If saying "Object doesnt support
> >> this property or method"
> >> Can anyone help? or is my code completely wrong?
> >>
> >> Thanks in advance
> >> David
> >>
> >>
> >
>
>

thanks for all your help lads
much appreciated

David


"Mike H" <(E-Mail Removed)> wrote in message
news:33CF02C6-47EA-4277-9868-(E-Mail Removed)...
> Try filling an odd numbered row because that's what your testing
>
> "David Kennedy" wrote:
>
>> Thanks for the swift reply,
>>
>> Fill color,
>>
>> for testing I filled in yellow on row2 col1
>> I applied your fix but the code doesnt seem to recognise the yellow
>> filled
>> field
>>
>>
>>
>>
>>
>> "Dave Peterson" <(E-Mail Removed)> wrote in message
>> news:(E-Mail Removed)...
>> > If Cells(iRow, iField).Font.ColorIndex = 6 Then
>> > or
>> > If Cells(iRow, iField).Interior.ColorIndex = 6 Then
>> >
>> > (Font color or fill color??)
>> >
>> > David Kennedy wrote:
>> >>
>> >> Hi,
>> >>
>> >> I have an excel file that has 17000 rows and 33 cols.
>> >> Some of the cells in the file are highlighted yellow.
>> >> I am trying to mark (with an X) in col 34 at the end what rows
>> >> contains
>> >> the
>> >> highlighted cell
>> >>
>> >> My code below:
>> >>
>> >> Dim rngCells As Range
>> >> Dim intRows As Long
>> >> Dim intFields As Long
>> >> Dim iRow As Long
>> >> Dim iField As Long
>> >>
>> >> Set rngCells = Application.ActiveWorkbook.ActiveSheet.UsedRange
>> >>
>> >> intRows = rngCells.Rows.Count
>> >> intFields = rngCells.Columns.Count
>> >>
>> >> For iRow = 1 To intRows Step 2
>> >> For iField = 1 To intFields
>> >> If Cells(iRow, iField).ColorIndex.Value = 6 Then
>> >> With Cells(iRow, 34)
>> >> .Value = "X"
>> >> End With
>> >> End If
>> >> Next iField
>> >> Next iRow
>> >>
>> >> Set rngCells = Nothing
>> >>
>> >> MsgBox "Yellow colors flagged"
>> >>
>> >> The code gives me an error on the first If saying "Object doesnt
>> >> support
>> >> this property or method"
>> >> Can anyone help? or is my code completely wrong?
>> >>
>> >> Thanks in advance
>> >> David
>> >
>> > --
>> >
>> > Dave Peterson
>>
>>
>>