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

--
Krussadam
-----------------------------------------------------------------------
Krussadams's Profile: http://www.excelforum.com/member.php...fo&userid=1913
View this thread: http://www.excelforum.com/showthread.php?threadid=54623

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.



Krussadams wrote:
>
> Hi all,
>
> I'm hoping someone can help me figure this one out. Each day I run
> 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. A
> varying percentage of each day's items are carried over to the next
> day's workbook. I use VLOOKUP to carry over the notes and other
> information I enter from day to day, but what I would like to do is
> also carry over the row color I had previously assigned - I know
> 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's
> not so easy too...as long as it saves me the time of manually going
> through and formatting row colors using "Compare Side by Side With..."
>
> Thanks!!
>
> -Keith
>
> --
> Krussadams
> ------------------------------------------------------------------------
> Krussadams's Profile: http://www.excelforum.com/member.php...o&userid=19135
> View this thread: http://www.excelforum.com/showthread...hreadid=546232

--

Dave Peterson

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.

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

aresen Wrote:
> 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.


--
Krussadams
------------------------------------------------------------------------
Krussadams's Profile: http://www.excelforum.com/member.php...o&userid=19135
View this thread: http://www.excelforum.com/showthread...hreadid=546232

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.

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:


aresen Wrote:
> 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.


--
Krussadams
------------------------------------------------------------------------
Krussadams's Profile: http://www.excelforum.com/member.php...o&userid=19135
View this thread: http://www.excelforum.com/showthread...hreadid=546232

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.

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!!!

aresen Wrote:
> 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.


--
Krussadams
------------------------------------------------------------------------
Krussadams's Profile: http://www.excelforum.com/member.php...o&userid=19135
View this thread: http://www.excelforum.com/showthread...hreadid=546232

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.

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

aresen Wrote:
> 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.


--
Krussadams
------------------------------------------------------------------------
Krussadams's Profile: http://www.excelforum.com/member.php...o&userid=19135
View this thread: http://www.excelforum.com/showthread...hreadid=546232