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

Kirnon

Board Regular
Joined
Apr 23, 2008
Messages
110
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.
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

Kirnon

Board Regular
Joined
Apr 23, 2008
Messages
110
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?

Code:
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
 
Upvote 0

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
83,100
Office Version
  1. 365
Platform
  1. Windows
Hello Kirnon
How are you highlighting the new info?
 
Upvote 0

Kirnon

Board Regular
Joined
Apr 23, 2008
Messages
110
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.
 
Last edited:
Upvote 0

Arithos

Well-known Member
Joined
Aug 14, 2014
Messages
598
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?
 
Upvote 0

Arithos

Well-known Member
Joined
Aug 14, 2014
Messages
598
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
 
Upvote 0

Arithos

Well-known Member
Joined
Aug 14, 2014
Messages
598
Found other threads on this forum about this


Code:
.Interior.ColorIndex > 0

Use this instead, of your

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

And it should work. :)
 
Upvote 0

Kirnon

Board Regular
Joined
Apr 23, 2008
Messages
110
JoeBloggs[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.
 
Upvote 0

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
83,100
Office Version
  1. 365
Platform
  1. Windows
I didn't make myself very clear.
Are you adding the colour, or are you using conditional formatting?
 
Upvote 0

Forum statistics

Threads
1,190,959
Messages
5,983,855
Members
439,867
Latest member
Shadrack

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top