Solved: An EXCEL to WORD Conversion question

[austenr]

I have a macro that makes a greenbar report (every other line light green) that works in EXCEL (Thanks DRJ) that I wish to work in Word. The code is below. How can I adapt this to Word?

Sub ColorRows()

Dim LastRow As Long
Dim LastCol As Long
Dim i As Long

LastRow = Cells.Find(What:="*", After:=Range("A1"), LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

LastCol = Cells.Find(What:="*", After:=Range("A1"), LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column

For i = 1 To LastRow Step 2
Range(Cells(i, 1), Cells(i, LastCol)).Interior.ColorIndex = 35
Next i

End Sub

Thanks

[Killian]

If you have 2002/3 (or even 2000, I'm not sure) then Table styles have this feature. You can set up a table style to have different formatting, including shading, on alternate rows (odd or even). You'd just need to copy across the data as a table from Excel and assign the style.

[austenr]

Thanks. Have a couple of questions. The macro is from EXCEL not the data. The data is already a text file. All I need to do is highlight every other row light green. I was using the Styles and could not figure out how to alternate every other row. Example please.

[Killian]

I've attached an example of what I mean. It's pretty basic but it should give you an idea.If you run the Excel macro from the same directory as the word file it should work.
I guess, ultimately you'd have a Word template set up and do a File...New from that and save it out
Here's the code in the workbook:Sub Data2Word()


Dim rng As Range
Dim wdApp As New Word.Application
Dim wdDoc As Word.Document
Dim t As Word.Range
Dim myWordFile As String

myWordFile = ThisWorkbook.Path & "\DocWithTableStyle.doc"

Set rng = Range("A1").CurrentRegion
rng.Copy

Set wdDoc = wdApp.Documents.Open(myWordFile)

Set t = wdDoc.Content
t.Paste
With t
.Style = "GreenBar"
End With

wdApp.Visible = True
wdApp.Activate

End Sub

[MOS MASTER]

Hi Killian, :D

Love this simple sollution! :clap:
In particular the use of the Table Style to get the formatting part done..(nice thinking there..)

[Zack Barresse]

I smell a Kbase entry (http://www.vbaexpress.com/kb/submitcode.php)!!! : pray2:

[MOS MASTER]

I smell a Kbase entry (http://www.vbaexpress.com/kb/submitcode.php)!!! : pray2:
Yepzz...The Please Keep It Simple entry! :rofl:
(Someone could have dealt with this issue with a whole lot more code...)

[Killian]

OK, I'll add that to my 'to do' list :whistle:
Needs a couple more lines - new file from a Word template, a little formatting to make sure it fits the page and maybe a save... Excel users don't seem to like seeing Word if they can avoid it :devil:

[MOS MASTER]

Excel users don't seem to like seeing Word if they can avoid it :devil:Yepz...is the same with Word users versus Excel! :devil:

Looking forward to you're entry.