Copy cell above if it has no colour fill. Macro.

[Kirnon]

Hi,

So I have a small problem. I have a bunch of data which covers over 13000 rows and 64 columns. This extract lists changes in information but it only highlights the changes anything that hasn't changed is left blank. The information is always listed as 1 line with all old information followed by the line below with no information except the new information. New information can be that the change is to blank. new information is highlighted in Yellow.

Therefore using F5 - Special - Blanks doesn't work.
e
I need a quick vb code which simply fills in info from the line above IF that cell does not have any colour in it. (R:-1C)

I hope that I have been clear. Thanks in advance.

Kirnon.

 

[Kirnon]

Arithos,

I really appreciate your help.

However it would seem that this is not doing anything at all?

Sub Macro1()'   Fill Change Cells with info from above.
    Dim r As Range
    
    LastRow = Sheets("Changes").Cells(Rows.Count, 1).End(xlUp).Row
    
    Set r = Sheets("Changes").Range("A2:AF" & LastRow)
    
    If r.Cells.Interior.ColorIndex > 0 Then
        r.Cells.FormulaR1C1 = "=R[-1]C"
    End If
End Sub

 

[Arithos]

Joe	Bloggs	[Yellow]
		Married[Yellow]
Andy[Yellow]		Married[Yellow]
Bob[Yellow]		Divorced[Yellow]

<tbody>
</tbody>

Rows are in pairs, Row 1 and row 2 are a pair Row 3 and row 4 are a pair.


I am unsure if this is any clearer.

Yup, got that, but there will be yellow cells in both pairs, not just the second row (in each pair)?

Should it not be like this:

Joe	Bloggs	[NO COLOR]
		Married[Yellow]
Andy[NO COLOR]		Married[NO COLOR]
Bob[Yellow]		Divorced[Yellow]

<tbody>
</tbody>

This is what I assumed/understood from earlier.

 

[Kirnon]

Hi Fluff,

The colours are added to the cells, no conditional formatting.

 

[Fluff]

When you say

Row 3 and row 4 are a pair.

Does this mean that Andy changed his name to Bob when he got divorced?

 

[Kirnon]

Hi Arithos,

No, if there is a change then the original and the cell below showing the change are coloured.

 

[Kirnon]

Fluff,

Yea - as I said Andy is a HUGE Prince/Symbol Fan. Sadly the Gov't would allow him to use a symbol as his name so he went with Bob.

 

[Fluff]

This

 If r.Cells.Interior.ColorIndex > 0 Then

should be

 If r.Cells.Interior.ColorIndex < 0 Then

But this will simply fill any blank cell.
Do you just need to look in all even numbered rows & fill from above if needed?

 

[Kirnon]

This is the Formula I would use if I was not needing a Macro and just putting everything on a new sheet.

Unfortunately it's not all even numbered rows occasionally it will show items that were deleted or added as single fully coloured rows.

=IF(AND(Changes!$A2<>"Changed",Changes!B2=""),Changes!B1,Changes!B2)

 

[Fluff]

Give this a go

Sub Kirnon()

    Dim LstRw As Long
    Dim WrkRng As Range
    Dim Cl As Range


    LstRw = Sheets("Changes").Range("A" & Rows.Count).End(xlUp).Row

    Set WrkRng = Sheets("Changes").Range("A2:AF" & LstRw)

    For Each Cl In WrkRng
        If Cl.Interior.ColorIndex > 0 And Cl.Offset(1, 0).Interior.ColorIndex < 0 Then
            Cl.Offset(1, 0) = Cl
        End If
    Next Cl
End Sub

 

[Arithos]

Hi Arithos,

No, if there is a change then the original and the cell below showing the change are coloured.

I do not know of a "one-step" way to do this, other then looping through. Could take some time doing that(running the macro). Let me know if that is an option. I