code to mark coloured cells in excel

[David Kennedy]

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

 

[Mike H]

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

 

[Dave Peterson]

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

(Font color or fill color??)

 

[Mike H]

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

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

 

[David Kennedy]

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

 

[Don Guillett]

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]

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

 

[Mike H]

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

 

[David Kennedy]

thanks for all your help lads
much appreciated

David

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