Your example is too screwed up to be much help.

Are you looking for a completely different macro that looks only at columns
A and B and only copies columns A and B (makes no difference about the color
in O).

Why would 5 and 6 go to row 8 on the other sheet?

(I understand that if I look at row 6, then sinceA6 is blank use A5 and
since B6 is not blank, use B6)

Another question: if A6 were blank, would A5 always have a value or would
one need to look at A4, then A3 to find a value?

Do you really just want all filled cells from A & B with emty cells paired
with filled cells filled from the value above?
--
Regards,
Tom Ogilvy


"Jeff at Robert Bosch Corp."
wrote in message
...
Here is my code so far.

Private Sub CommandButton1_Click()
Dim rng As Range, rng1 As Range
Dim cell As Range
Set rng = Range(Cells(1, "O"), Cells(Rows.Count, "O").End(xlUp))
For Each cell In rng
If cell.Font.ColorIndex = 6 Or cell.Font.ColorIndex = 3 Then
If rng1 Is Nothing Then
Set rng1 = cell
Else
Set rng1 = Union(rng1, cell)
End If
End If
Next cell
If Not rng1 Is Nothing Then
rng1.EntireRow.Copy Destination:= _
Worksheets("Sheet3").Cells(Rows.Count, 1).End(xlUp)
Else
MsgBox "No cells met criteria"
End If

This copys a row if the font in column O is red or yellow.
I am also trying to see if it has text in columns A or B and if there is
no
contents, I want it to copy the cell above it.
For example:
A
-------------------------
O
5 1 1
6 1
7
8
red font

The code would then copy cells A5 & B6 to A8 & B8 on the other sheet