How to move every 2 cells, Excel 2000 & 2003

[jfcby]

Hello,

I'm working on a project that I tring to put a color in every other
cell background but my code is not working correct.

My data looks like this example:

Column1 Column2 column6 column7
aaa bbb ccc ccc
aaa bbb ccc ccc
aaa bbb ccc ddd
aaa bbb ccc ddd

Now I'm tring to put a cell background color Column1 & Column2 & Column
6 & Column7 Row2, skip to Row4, skip to row6. But my code below will
only skip the first row but then it will not skip rows it put color in
every row.

Sub CellColor()
Dim Cell As Object, r As Range
Set r = Range("A1:C1")
For Each Cell In Range("A:A")
Cell.Offset(2, 0).Select
With Selection.Interior
.ColorIndex = 15
.Pattern = xlSolid
End With
Next
End Sub

Thank you for your help,
jfcby

 

[Pete_UK]

Try single-stepping through your code (with F8) to see what is
happening.

Here's a different way of doing it, using conditional formatting:

http://www.cpearson.com/excel/banding.htm

Hope this helps.

Pete

 

[jfcby]

Hello Pete,

I single-stepped through the code (with F8) and when the code first
starts it will skip to the 2 row after then it goes through individual
cells. I've worked on this code now for 2 days and searched through the
google groups tring everything I could with no luck. since I've been
unsuccessful with this code a friend give me the below code but it
highlights a complete row can it be modified to high the cells as
described in my previous post. I also tried to modify this code but
with no luck.

This codes works with highlighting a complete row:
Sub CellColorTest()
Dim iRow As Integer
For iRow = 2 To Range("A1").SpecialCells(xlLastCell).Row Step 2
Rows(iRow - 1).Interior.ColorIndex = xlNone
Rows(iRow).Interior.ColorIndex = 40
Next iRow
End Sub

Also I have another one that does not work but maybe it can be modified
I've tried with no luck:
Sub ColumnMoveRightTEST()
Dim RowNdx As Long
Dim ColNdx As Integer
Range(ColNdx).Select
Do Until ActiveCell.Value = ""
RowNdx = 2
ColNdx = 1
Cells(RowNdx, ColNdx).Value = 123
ColNdx = ColNdx + 1
Cells(RowNdx, ColNdx).Value = 234
ColNdx = ColNdx + 1
Cells(RowNdx, ColNdx).Value = 235
Loop
End Sub

Thank you for your help,
jfcby

 

[Pete_UK]

Try this, then:

Sub CellColorTest_2()
Dim iRow As Integer
For iRow = 2 To Range("A1").SpecialCells(xlLastCell).Row Step 2
Rows(iRow - 1).Interior.ColorIndex = xlNone
Cells(iRow, 1).Interior.ColorIndex = 6
Cells(iRow, 2).Interior.ColorIndex = 6
Cells(iRow, 6).Interior.ColorIndex = 6
Cells(iRow, 7).Interior.ColorIndex = 6
Next iRow
End Sub

A variation of your second macro - you can see how to specify the
columns you want coloured. ColorIndex 6 is bright yellow - change to
suit.

Hope this helps.

Pete

 

[jfcby]

Hello Pete,

The code works great!

Thank you for your help,
jfcby

 

[Pete_UK]

You're welcome - thanks for feeding back.

Pete

Hello Pete,

The code works great!

Thank you for your help,
jfcby