Formatting a table in Word based on contents of cells.

[Ryan]

Hello,

I'm trying to figure out how to write a Macro for
formatting cell-colour (colour and intensity of colour)
based on values contained in a table. The table is about
500 rows and 10 columns. I want to manually select all
500 rows for 2 of the columns, and have a Macro change the
background-colour of the first coloumn of selcted cells
based on the contents of both the first column and the
second column. The cells of the first column contains 1
of 3 variables (C,H, or E), and the cells of the second
column contains 1 of 10 values (0-9). I want the macro to
change the properties of only the first column, with
colour type based on the variables (C=red, H=blue,
E=green), and intensity based on the values (0=0%, 1=10%,
2=20%,...,8=80%, 9=90%). It takes forever to do this
manually, and I'm having difficulty writing a Macro.

I appreciate any guidance.

[Doug Robbins - Word MVP]

Hi Ryan,

I don't know of any intensity setting that you can apply either manually or
with code to colours.

The following code however will apply colour to the first cell in each row
corresponding to the colour code in that cell:

Dim colcode As Range, i As Integer
For i = 1 To ActiveDocument.Tables(1).Rows.Count
Set colcode = ActiveDocument.Tables(1).Cell(i, 1).Range
colcode.End = colcode.End - 1
If colcode = "C" Then
ActiveDocument.Tables(1).Cell(i,
1).Shading.BackgroundPatternColorIndex = wdRed
ElseIf colcode = "H" Then
ActiveDocument.Tables(1).Cell(i,
1).Shading.BackgroundPatternColorIndex = wdBlue
Else
ActiveDocument.Tables(1).Cell(i,
1).Shading.BackgroundPatternColorIndex = wdGreen
End If
Next i

If you post back with more information or how you are manually setting the
intensity, we may be able to automate that as well.

Please do not post the same question separately to multiple newsgroups.
Rather, if you are not sure which is the most appropriate ng, insert the
names of those which you think might be appropriate into the header of a
single message. When you do that, an answer in one ng will automatically
appear in all of the ngs to which you posted. That will make it easier for
you to find responses and can also reduce the possibility of someone
developing an answer to a question that has already been answered.

Please post any response to the newsgroups for the benefit of others who may
also be following the thread.

Hope this helps,
Doug Robbins - Word MVP
"Ryan" <tre...@hotmail.com> wrote in message
news:42df01c1fe9a$d6b6afc0$a4e62ecf@tkmsftngxa06...

[Ryan Doherty]

Hello,

Thank you for your assistance.

When I used the term Intensity, I meant "Pattern Style". Manually, I had
set this by right clicking on the cell, selecting "Borders and Shading",
selected the "Shading" tab, and would manually select "Patterns: Style" of
90% for the value of 9, 80% for the value of 8, etc.

Do you know which parameter is associated with the Pattern Style?

Thanks,

Ryan

"Doug Robbins - Word MVP" <rdou...@bigpond.net.au> wrote in message
news:u8Z92As$BHA.2540@tkmsftngp05...

[Ryan Doherty]

Hello Doug,

I just tried your Macro, and it almost everything I hoped for. Although,
your Macro did the same as the "Fill" command (right clicking on the cell,
selecting "Borders and Shading", selecting the "Shading" tab, and selecting
a colour from the upper half "Fill"). What I was wanting was to keep the
"Fill" property at "none", and having the Macro change the "Patterns"
property (right clicking on the cell, selecting "Borders and Shading",
selecting the "Shading" tab, and selecting a "Pattern Style" percentage from
the bottom half, as well as a "Pattern Colour" based on the C/H/E variable
and the 0-9 value). I figure that the Macro would be pretty much identical
to the one you just wrote, and instead of having
"ActiveDocument.Tables(1).Cell(i,
> 1).Shading.BackgroundPatternColorIndex", it would be
"ActiveDocument.Tables(1).Cell(i,
> 1).Shading.BackgroundPatternStyleIndex" or something similar.

I really appreciate your help.

Ryan

"Doug Robbins - Word MVP" <rdou...@bigpond.net.au> wrote in message
news:u8Z92As$BHA.2540@tkmsftngp05...

[Doug Robbins - Word MVP]

Hi Ryan,

What you are talking about is the texture property.

The following code will apply a texture based on the contents of the second
cell in each row:

Dim colcode As Range, i As Integer, Texcode As Range, Texstr As
WdTextureIndex

For i = 1 To ActiveDocument.Tables(1).Rows.Count
Set colcode = ActiveDocument.Tables(1).Cell(i, 1).Range
colcode.End = colcode.End - 1

Set Texcode = ActiveDocument.Tables(1).Cell(i, 2).Range
Texcode.End = Texcode.End - 1
If Val(Texcode) = 0 Then
Texstr = wdTextureNone
Else
Texstr = wdTexture & 10 * Val(Texcode) & Percent
End If
ActiveDocument.Tables(1).Cell(i, 1).Shading.Texture = Texstr

If colcode = "C" Then
ActiveDocument.Tables(1).Cell(i,
1).Shading.BackgroundPatternColorIndex = wdRed
ElseIf colcode = "H" Then
ActiveDocument.Tables(1).Cell(i,
1).Shading.BackgroundPatternColorIndex = wdBlue
Else
ActiveDocument.Tables(1).Cell(i,
1).Shading.BackgroundPatternColorIndex = wdGreen
End If
Next i

Please post any response to the newsgroups for the benefit of others who may

also be following the thread.

Hope this helps,
Doug Robbins - Word MVP

"Ryan Doherty" <ryand...@rogers.com> wrote in message
news:kXBF8.35000$ah_....@news01.bloor.is.net.cable.rogers.com...