conditional formatting multiple tables using Macro in MS Word

[scOOt3r]

Hello,

This is my first attempt at learning/writing VBA code. I'm trying to write a macro in word that would do the following.

Scan through the word document for tables. For every table,
1. Shade the row blue if the value in the corresponding first column is TRUE and if it's false, shade it green
Tables have differing column widths (These are balance sheets with headers embedded in a word doc.)- I mention this, because I did a search and I came across a post that said , it's not possible to change/manipulate a table with different column widths.

2.After the macro has scanned and applied the relevant colours to the rows, it should hide the first column for every table.

we came up with a similar one in excel (It's a li'l different though) , But I'm not sure if I can apply it to word.

Sub AlternatePrintRowColor_Blue()

' Removes any previous shading to start clean
Cells.Select
With Selection.Interior
.Pattern = xlNone
.TintAndShade = 0
.PatternTintAndShade = 0
End With
Range("A1").Select

' Adds Shading based on True/False in Column A (True gets shading)
Const TEST_COLUMN As String = "A" '<=== change to suit
Dim LastRow As Long
Dim cell As Range
sSheetName = ActiveSheet.Name

With Worksheets(sSheetName)
LastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row

For Each cell In Range("A2:A" & LastRow)
If cell.Value = "TRUE" Then
cell.EntireRow.Interior.Color = RGB(190, 227, 149)
Else
cell.EntireRow.Interior.ColorIndex = xlNone
End If
Next
End With

End Sub


I appreciate any help/insight on this one! Thanks a ton!

Cheers!

[Frosty]

1. Hiding rows in word tables is not quite the same as in Excel. You can apply font formatting "hidden" to an entire row, but that may or may not be same result.

2. You can affect columns of different widths. What is difficult in Word is dealing with tables which contained *merged* cells (whether vertically merged or horizontally merged). At that point, you start having trouble referring to individual rows/columns via code.

3. Can you post a sample document with a sample table of the type you're dealing with, including any headers, structure etc... no sensitive data, please, but a table structure of the type you want the macro to run on would be very useful.

4. Use the VBA tags, it will make your posted code much easier to read.

5. Lastly, since this is your first post: if you have cross-posted this question elsewhere, please provide links to all of the places you've crossposted. It can be very frustrating to try to help someone only to find out they have been asked and answered elsewhere.

[Frosty]

That said, this may guide you in the right direction, although it can fail depending on your table construction.

Sub Demo()
Dim oTable As Table
Dim oRow As Row
Dim sCellText As String
Dim iColumnToTest As Integer

iColumnToTest = 2
For Each oTable In ActiveDocument.Tables
'this will fail if you have vertically merged cells
For Each oRow In oTable.Rows
'get the cell text
sCellText = oTable.Cell(oRow.Index, iColumnToTest).Range.Text
'remove the end of cell character
sCellText = Left(sCellText, Len(sCellText) - 2)
With oRow.Shading
.Texture = wdTextureNone
.ForegroundPatternColor = wdColorAutomatic
'test term
If sCellText = "TRUE" Then
.BackgroundPatternColor = RGB(190, 227, 149)
Else
.BackgroundPatternColor = wdColorAutomatic
End If
End With
Next
'apply font formatting of "hidden"
'(will still show when viewing paragraphs -- check print preview)
oTable.Rows(1).Range.Font.Hidden = True
Next
End Sub

[Frosty]

I just noticed that you want to "hide" the first column, not the first row. This will not work the same way in Word as hiding a row (it will hide the data, but not the actual column width).

There are a couple of ways to approach this, if you wish to preserve the data... but you'll need to post a sample table with a before and after of what you'd want to have done. You could
1. delete the column
2. change the width of the column so that it is effectively "hidden", although the data remains. You would probably want another way to show this hidden data... and you would probably want to autofit the rest of the columns.

It may help you to record some macros in Word to see what you can do. Just start by selecting the area you want to work with, and then click various formatting buttons...

[scOOt3r]

Thanks for your input. I've attached a sample document of how we want it to look after the macro is run (except for the first column visibility. I haven't hidden it in the sample attached)

We changed our mind on shading it green. We'd rather leave it as is , if the value in the first column is false.

My apologies for not posting the code within the VBA tags. I'll keep that in mind the next time I post one. I haven't posted this question anywhere else. :)

Thanks again!

[scOOt3r]

I just noticed that you want to "hide" the first column, not the first row. This will not work the same way in Word as hiding a row (it will hide the data, but not the actual column width).

There are a couple of ways to approach this, if you wish to preserve the data... but you'll need to post a sample table with a before and after of what you'd want to have done. You could
1. delete the column
2. change the width of the column so that it is effectively "hidden", although the data remains. You would probably want another way to show this hidden data... and you would probably want to autofit the rest of the columns.

It may help you to record some macros in Word to see what you can do. Just start by selecting the area you want to work with, and then click various formatting buttons...

Thanks for the code above.

Deleting the column would be great! I didn't know if that was possible as I didn't want word to mess up the other column widths. I tried recording a macro for small actions, but I didn't get too far.
After I cleared the "it won't let me edit with the 'cassette cursor'" hurdle. I hit F5 and tried to edit the first row, but the code it generated didn't exactly work as I expected it to.

I tried to run the above code and the only change i noticed was that it removed all of my previous formatting.

[Frosty]

Well, unfortunately, your table is a disaster of merged cells. In the sample you provided, it doesn't appear that you have any vertically merged cells, which means you will be able to access individual rows in the rows collection, but you won't be able to access individual columns, whether to hide or delete. Which means you need to probably do a bit of trickery (and this trickery could easily be unreliable).

The good news is that it's the first column you want to get rid of, so that provides some flexibility. Try the following code...

Sub Demo()
Dim oTable As Table
Dim oRow As Row
Dim sCellText As String
Dim iColumnToTest As Integer

iColumnToTest = 1
For Each oTable In ActiveDocument.Tables
'this will fail if you have vertically merged cells
For Each oRow In oTable.Rows
'get the cell text
sCellText = oTable.Cell(oRow.Index, iColumnToTest).Range.Text
'remove the end of cell character
sCellText = Left(sCellText, Len(sCellText) - 2)
With oRow.Shading
'test term
If sCellText = "TRUE" Then
.Texture = wdTextureNone
.ForegroundPatternColor = wdColorAutomatic
.BackgroundPatternColor = RGB(190, 227, 149)
Else
'.BackgroundPatternColor = wdColorAutomatic
End If
End With
'now delete the first cell
oRow.Cells(1).Delete
Next
Next
End Sub

[Frosty]

Actually, on the second table of your sample, that cells(1).delete causes a bit of an issue...

you could try oTable.Rows.LeftIndent = 0 before the very last next, but that may not be applicable to all the different types of tables.

Unfortunately, you have a very messy table, structurally. It is difficult to reliably program for messy tables. A For Each loop which goes through all tables in a document (you've provided two samples, each significantly different from each other) will be as complex as each different table structure.

It may be better to simply use the formatting, and then manually delete the column.

[scOOt3r]

I've tried the code above and it worked for most of the tables.
Regarding the delete option,.. would it make more sense to have another loop go through all the tables to delete the entire first column? (as opposed to deleting every cell after reading it?)

[scOOt3r]

never mind that last question..
I tried this and I got an error about the mixed column width(s).


For Each oTable In ActiveDocument.Tables
oTable.Columns(1).Delete
Next

[Frosty]

Exactly. You can still separate out the functionality, but with merged cells, you invalidate that entire collection (columns or rows, depending on the kind of merge)... So you would always have to delete cell by cell. Finding the right cell is the trick, although in this case, it's not so hard

Honestly, those tables are probably something you should address, although that will be a considerably more involved set of macros

[scOOt3r]

Unfortunately, the word document is the output of a tool we use here that lacks the functionality of conditional formatting. We've now come up with a manual work around.

Thanks for all your help!! :)

[scOOt3r]

I don't see the option to mark this thread as "Solved" under "Thread Tools". Is there some place else I should be looking?