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]

So I have been having a go on my own but its just copying everything from the first row down to the end. What am I doing wrong?

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.Interior.Color = False Then
        r.Cells.FormulaR1C1 = "=R[-1]C"
    End If
End Sub

 

[Fluff]

Hello Kirnon
How are you highlighting the new info?

 

[Kirnon]

Hi Fluff,

Uh... I am not sure how to put a grid into the post... so....

Column/Row A | B | C
1 Joe | Bloggs | [Yellow]{blank}
2 {blank} | {blank} | [Yellow]Married
3 [Yellow]Andy | {blank} | [Yellow]Married
4 [Yellow]Bob | {blank} | [Yellow]{blank}

Andy is obviously a big fan of Prince hence having no surname.

However I hope the above gridless grid gives an idea. The information appears with the colours already on it and if there are no changes the cells below the original info do not have values - I need them to have values. I recognise my problem with my own little creation but I don't know how to create something correctly.

Edit: The blank fills every 2nd row.

 

[Arithos]

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.

Your setup is as follows?

Row1, old line
Row2, new information line.
Row3, old line
etc...
Row2, has yellow color if there is new information?

From what I can understand, your formula will insert "=R[-1]C" if its not a cell with new information. (no Coloring, yellow)

Instead of "I need a quick <acronym title="vBulletin" style="border-width: 0px 0px 1px; border-bottom-style: dotted; border-bottom-color: rgb(0, 0, 0); cursor: help; color: rgb(51, 51, 51); background-color: rgb(250, 250, 250);">vb</acronym> code which simply fills in info from the line above IF that cell does not have any colour in it. (R:-1C)"

You want a quick vb code which simply fills info from the row above, if the cell in This row, does not contain a color. (just took some time to interpret for me :P )
And you need to go on color, since some "new" fields might be "", and that is the change in itself.

Am I understanding this correctly?

 

[Kirnon]

Hi,

Thats right. I wish I had been able to explain this as simply as you have.

 

[Arithos]

Hi Fluff,

Uh... I am not sure how to put a grid into the post... so....

Column/Row A | B | C
1 Joe | Bloggs | [Yellow]{blank}
2 {blank} | {blank} | [Yellow]Married
3 [Yellow]Andy | {blank} | [Yellow]Married
4 [Yellow]Bob | {blank} | [Yellow]{blank}

Andy is obviously a big fan of Prince hence having no surname.

However I hope the above gridless grid gives an idea. The information appears with the colours already on it and if there are no changes the cells below the original info do not have values - I need them to have values. I recognise my problem with my own little creation but I don't know how to create something correctly.

Edit: The blank fills every 2nd row.

Go Advanced -> (many options), choose a table or what you want -> paste info.

Most helpful would be the excel addin to take a html "picture" of your workbook.
Like excel Jeanie
Download


More here

http://www.mrexcel.com/forum/excel-questions/628649-recommended-add-ins-links.html

 

[Arithos]

Found other threads on this forum about this

.Interior.ColorIndex > 0

Use this instead, of your

[COLOR=#333333]Interior.Color = False Then
[/COLOR]

And it should work.

 

[Kirnon]

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.

 

[Fluff]

I didn't make myself very clear.
Are you adding the colour, or are you using conditional formatting?