Cells(row, j).Interior.colorindex

K

KT_1010

I wanted to set the color for a row to a specific value

for i = 1 to 30
Cells(ActiveCell.row, i).interior.colorindex = colIndex
next i

which should set the property for the first 30 cells of the current
row

The debugger aborts when it comes to this line, and I can't figure out
why

I've tried
myRow = ActiveCell.row (which should be the row
of the target cell)
With Worksheet("target").Cells(myRow, i)
.interior.colorindex = 33
.font.colorindex = 2
End with


which also doesn't work.
I had thought that Cells(i,j) assumed the current worksheet (and of
type Range)
(ActiveSheet.Cells(i,j) was a Range of one cell

also ActiveSheet.Rows(myRow).interior.colorindex also fails

how do I specify a range object that is the row that contains the
ActiveCell

(i've been trying to use the object browser to find a combination that
has the right syntax without much success.)
 
J

Jim Cone

Your first loop works for me in xl2003.
"Range(Cells(ActiveCell.Row, 1), Cells(ActiveCell.Row, 30)).Interior.ColorIndex = colIndex" also works.

How are i and colIndex declared?
What is the value of colIndex?
Is the sheet protected?
Is any part of the row merged?
--
Jim Cone
Portland, Oregon USA
Shade Data Rows add-in: http://tinyurl.com/ShadeData

..
..
..

"KT_1010" <[email protected]>
wrote in message
I wanted to set the color for a row to a specific value

for i = 1 to 30
Cells(ActiveCell.row, i).interior.colorindex = colIndex
next i

which should set the property for the first 30 cells of the current row
The debugger aborts when it comes to this line, and I can't figure out why
I've tried
myRow = ActiveCell.row (which should be the row
of the target cell)
With Worksheet("target").Cells(myRow, i)
.interior.colorindex = 33
.font.colorindex = 2
End with

which also doesn't work.
I had thought that Cells(i,j) assumed the current worksheet (and of type Range)
(ActiveSheet.Cells(i,j) was a Range of one cell
also ActiveSheet.Rows(myRow).interior.colorindex also fails

how do I specify a range object that is the row that contains the ActiveCell
(i've been trying to use the object browser to find a combination that
has the right syntax without much success.)
 
K

KT_1010

Sub setHighlight(row As Long, cindex As Integer)
Dim ix As Long
Application.ScreenUpdating = False
For ix = 1 To 25
With Cells(row, ix)
.Font.ColorIndex = 2
.Interior.ColorIndex = cindex

End With
Next ix
Application.ScreenUpdating = True
End Sub

and call setHighlight(ActiveCell.Row, 23) ' 23 is one of the 56
possilble indexs to Colors
 
P

Peter T

There's nothing obviously wrong with your routine.

FWIW no need to loop, eg

With Cells(row, 1).Resize(1, 25)
or
With Range(Cells(row, 1), Cells(row, 25))
.Font.ColorIndex = 2
.Interior.ColorIndex = cindex
End With

and if not looping no need to disable screenupdating just for that

Small thing, these days better to use As Long rather than As Integer, not
that it'll make any difference in your routine.

Regards,
Peter T
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Formula Cell Error 5
Verify selection of cells 2
VBA: Paste in a range 6
Code copies ONLY last j from sheet 9
converting a for next loop into a for each loop 2
Active Cell 5
Remove Identical words 0
Loop with Step 1

Top