Carying over formatting btw. workbooks

K

Krussadams

Hi all,

I'm hoping someone can help me figure this one out. Each day I ru
a report of material to purchase, which is saved in an Excel workbook.
Depending on the status of each item (row), I color code the row.
varying percentage of each day's items are carried over to the nex
day's workbook. I use VLOOKUP to carry over the notes and othe
information I enter from day to day, but what I would like to do i
also carry over the row color I had previously assigned - I kno
VLOOKUP does not support this. Column A contains all the part numbers
and is the column I use for my VLOOKUP formulae. Anyone know of a way
preferably an easy one, that this can be done. Well, even one that'
not so easy too...as long as it saves me the time of manually goin
through and formatting row colors using "Compare Side by Side With..."


Thanks!!

-Keit
 
D

Dave Peterson

You might be able to create a macro to do the =vlookup() and copy the format of
the row to its new location.

But I've always found that working with colors (fonts or fills) is more
difficult. You can' filter on the color (yet). You can't sort on the color.
All that stuff that may come in handy.

I'd use another column to actually put a a code/word/phrase that corresponds to
the status. Then I could use =vlookup() to retrieve that value and maybe even
use format|conditional formatting to color the row automatically.

If you have more than the 3 colors that conditional formatting allows (plus the
normal format), you could use a macro that would apply the color.
 
A

aresen

Sub CopyColor()
Dim sht As String, LR As Long, cell As Range, cnt As Long

sht = InputBox("Enter prior day's worksheet name:")
LR = Cells.SpecialCells(xlCellTypeLastCell).Row
Range("A:A").EntireColumn.Insert
Range("A1:A" & LR).FormulaR1C1 = "=MATCH(RC2,'" & sht & "'!C1,0)"

On Error Resume Next
cnt = Range("A:A").SpecialCells(xlCellTypeFormulas, 1).Count

If Err = 0 Then
For Each cell In Range("A:A").SpecialCells(xlCellTypeFormulas, 1)
cell.EntireRow.Interior.ColorIndex =
Sheets(sht).Cells(cell.Value, 1).Interior.ColorIndex
Next
End If

Range("A:A").EntireColumn.Delete
End Sub

The above macro assumes the value in column A of the current day's
worksheet is being looked up in column A of the prior day's worksheet.
You can modify this in the macro line: Range("A1:A" & LR).FormulaR1C1 =
"=MATCH(RC2,'" & sht & "'!C1,0)"
where RC2 refers to current row and column 2 and the !C1 refers to
column 1. The reason it's looking at column 2 is because the macro
inserts a column ahead of the data, causing column A to become column
B. After it completes coloring, it blows away the inserted column so
you're back to where you were except with color.
The macro is prompting you for the prior day's worksheet name since I
assumed it's always different. I hard-coded the columns because I
assumed these wouldn't change.
If you have any questions, reply.
 
K

Krussadams

Thank you very much, aresen!! It looks like this is just what the doctor
ordered. However, I just tried it out on sample spreadsheet here at
home, and when creating the macro and testing it out, I get a compile
error on the following line:

cell.EntireRow.Interior.ColorIndex =

Any ideas what may be causing this?


Thanks!!!

-Keith
 
A

aresen

When I sent the reply, that lione and the one below it were all on one
line. Type a space followed by an underscore after the equal sign to
indicate a continuation. Otherwise, put it all on one line.
 
K

Krussadams

Hi aresen,

Thank you. As is obvious, VBA is not my strong suit. I tried running
this macro again on a couple sample spreadsheets at home, and the
compile error is fixed. The macro appears to function just fine,
however the rows do not color. I first tried putting numeric values in
column A for the first 10 rows and had them in numeric order. I colored
each row differently. I then saved this book, and opened a second,
where I inserted the macro. I then entered the same numeric values in
column A, first 10 rows - but at first tried them in a wholly separate
order. I left them uncolored, and tried the macro. I could see it
insert the column and remove it, but no coloring appeared in the rows.
I also tried it in the same numeric order as the other book, and still
no luck. I'm using Excel 2002 here at home (2003 at work), and I just
want to make sure I'm doing everything correctly...for I'm sure this is
user error...:redface:
 
A

aresen

I didn't realize the worksheet was in a different workbook. The macro I
gave you "assumes" the same workbook. This is easily correctable (is
that spelled right?). However, to make it clean, we could be looking at
a workbook with a different name (obviously) but the same worksheet
name. If so, we can prompt for the workbook name and get the data. Is
the other workbook in the same directory? Do you follow some kind of
naming convention? If so, we could look for the most recent. There are
lots of possibilities.
But first, try copying the worksheet into your current version giving
it some other name like "target". Then run the macro responding with
"target" as the desired sheet name. Does it color the way you want? If
so, we can add code to get the workbook name, path, whatever
to fetch the correct sheet.
 
K

Krussadams

Ah HA!! That would certainly explain it. I just tried it with by
running the macro on a second sheet in the same workbook, and it worked
PERFECTLY.

True, the data is in another workbook. I generate the data each day
from a program named SAP, and open the output using Excel. By deault,
the sheet is the lone one in the book, and it is given the name of the
book. To keep things simple, I name everything just by date (in the
American fashion). So, for example, today's would be named
"05-29-06.xls" Sometimes, depending on demand, I run it more than once
a day, in which case I would title the second one "05-29-06 - PM.xls"
All sheets are stored in the same directory.

Thank you so much for all your help!!!
 
A

aresen

One more thing; I suggest the convention of naming files using a
yymmdd format. That way, they will always sort in a known order. You
can then use the FileDateTime function to determine the latest
workbook. Respond if you would like additional information/examples.
 
K

Krussadams

Hi aresen,

That is certainly doable, especially at this point in time. I've only
been with the company for about 4 weeks, so there's a minimal number of
files to convert to that convention...and it something I can do going
forward...


Thanks!!

-Keith
 
Top