Solved: Fill Interior Color and Sort

[YellowLabPro]

Could someone assist me w/ multi part issue please?

There are two parts to this. Request 1 has one part and Request 2 has two parts.

1) I am attempting to fill the cells w/ an interior color.
For the this section of "UsedRange", how to fill it w/ a certain interior color, lets say the soft yellow color?

[vba]
Sub Importer()
Dim LastRow As Long, LastRowSrc As Long, LastRowDst As Long, i As Long
Dim ws1 As Worksheet, ws2 As Worksheet, ws3 As Worksheet, C As Range
Dim rng1 As Range, rng2 As Range
Set ws1 = Workbooks("TGS Item Record Creator.xls").Sheets("Record Creator")
Set ws2 = Workbooks("MasterImportSheetWebstore.xls").Sheets("TGFF")
Set ws3 = Workbooks("TGSImporter.xls").Sheets("Update")
LastRowSrc = ws1.Cells(Rows.Count, 1).End(xlUp).Row
'Set ws3 = Workbooks("Complete_Upload_File.xls").Sheets("EC Products")
'LastRow = ws3.Cells(Rows.Count, 1).End(xlUp).Row
LastRow = ws2.Cells(Rows.Count, 1).End(xlUp).Row

ws3.Range("A1:H1") = Array("Origin", "Item#", "Record Description", "Dept", _
"Cat", "Qty", "Cost", "Price")
Rows("1:1").HorizontalAlignment = xlCenter
Rows("1:1").Font.Bold = True
Cells.Columns.AutoFit
Rows("1:1").HorizontalAlignment = xlLeft
Rows("1:1").Font.Bold = True

ws3.UsedRange.Offset(1, 0).ClearContents

ws1.Range("u5:u" & LastRowSrc).Copy
ws3.Range("b2").PasteSpecial Paste:=xlPasteValues

ws1.Range("w5:w" & LastRowSrc).Copy
ws3.Range("c2").PasteSpecial Paste:=xlPasteValues

ws1.Range("ab5:ab" & LastRowSrc).Copy
ws3.Range("d2").PasteSpecial Paste:=xlPasteValues

ws1.Range("ac5:ac" & LastRowSrc).Copy
ws3.Range("e2").PasteSpecial Paste:=xlPasteValues

ws1.Range("Aa5:Aa" & LastRowSrc).Copy
ws3.Range("f2").PasteSpecial Paste:=xlPasteValues

ws1.Range("x5:x" & LastRowSrc).Copy
ws3.Range("g2").PasteSpecial Paste:=xlPasteValues

ws1.Range("z5:z" & LastRowSrc).Copy
ws3.Range("h2").PasteSpecial Paste:=xlPasteValues

ws3.Range(Cells(2, 1), Cells(ws3.Cells(Rows.Count, 2).End(xlUp).Row, 1)) = "New Record"
[/vba]


Second Question:
A)Sort the entire range based on column B:
If after the sort takes place then look to see if the value in the immediate cell matches, if so place the term "Dupe" in the corresponding cell in Column I and fill that w/ Red.
B) Sort option #2-
Sort on column C

[mdmackillop]

Part 1
[VBA]
ActiveSheet.UsedRange.Interior.ColorIndex = 36
[/VBA]

[YellowLabPro]

Good Morning or Afternoon MD,
Thanks for the line of code. As you know there are two parts to populating my worksheet, copying data from ws1 and ws2. I want to color only the cells copied over from ws1. The AcitveSheet.UsedRange is filling all the cells from both sheets. So where ws1 only copies over about 50 records, the fill is occurring into 7200 records, the additional ones from ws2. I placed the line of code prior to copying the data from ws2, which I thought would only look at the UsedRange from ws1, evidently not the case.
So I changed the line of code from ActiveSheet.UsedRange.Interior.ColorIndex = 36
to
ws3.Cells(Rows.Count, 1).End(xlUp).Interior.ColorIndex = 36

But this only colors the last non-blank cell in column 1, not the entire row and not up. I would also like to limit the cell fill to stop at row number 2, not filling row 1.

Thanks in advance....

YLP

[uksrogers]

Just an idea. When you do the pastespecial you are specifying only to paste the values. What if you were to apply the colour formatting to ws1 and then paste the values and the formatting. This could be done via a separate .pastespecial xlpasteformats line, or by a single .pastespecial xlpasteall instead.

uksrogers

[YellowLabPro]

Hi Uk,
No not really an option. The sheet it is copying from is already colored for id reasons.

thanks for the idea....

YLP

[uksrogers]

If the items from WS1 are being copied into a bigger list (WS2) then i guess you will need to do a bit of calculation and work out the range you want to apply the colour to. You know from ws1 how many rows you are copying over by deducting the first row in the range you are copying from lastrowsrc (let's call this ws1rows). Then you know when you are pasting to the larger sheet, you know the row you are starting the paste from. You then do something along the lines of

[vba]ws2.Range(Cells(ws2startrow,1),Cells(ws2startrow + ws1rows,1).entirerow.Interior.ColorIndex = 36[/vba]

or

[vba]ws2.Range("A" & ws2startrow, "A" & ws2startrow + ws1rows).entirerow.Interior.ColorIndex = 36[/vba]

If that still doesn't quite do it for you.. then i might have misunderstood what you are trying to do.

uksrogers

[YellowLabPro]

Hi Uk,
thanks. I am sorry that you had to go to any trouble. I had solved it a little earlier and to save anyone time, had marked it as Solved.

Here is what I used:
ws3.Range(ws3.Cells(1, 8), ws3.Cells(ws3.Cells(Rows.Count, 1).End(xlUp).Row, 1)).Interior.ColorIndex = 36