code to mark coloured cells in excel
 

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

code to mark coloured cells in excel
 

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

code to mark coloured cells in excel
 

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

code to mark coloured cells in excel
 

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" wrote in message
...
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

code to mark coloured cells in excel
 

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" wrote in message
...
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

code to mark coloured cells in excel
 

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

"David Kennedy" wrote in message
...
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

code to mark coloured cells in excel
 

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

"Don Guillett" wrote in message
...
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

"David Kennedy" wrote in message
...
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

code to mark coloured cells in excel
 

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

"Don Guillett" wrote in message
...
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

"David Kennedy" wrote in message
...
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

code to mark coloured cells in excel
 

thanks for all your help lads
much appreciated

David


"Mike H" wrote in message
...
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" wrote in message
...
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