Finding Cell Contents

[Guest]

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 B -------------------------
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

 

[Tom Ogilvy]

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."

 

[Guest]

After the first macro copys all the rows that have r&y font, if there is no
value in A or B then I want it to copy the next value above. If this has to
be done under another command button that is fine. I guess what I am asking
doesn't depend on the first macro. It may be 1,2,3,4,5... rows above.
Columns A & B have different text in them as you go down the columns. So, it
has to work this way where it searches for the text above, and doesn't just
fill all the cells in the new sheet with the cells from the old that have
text.

Please let me know if you still don't know what I am trying to accomplish.

Thanks