Avatar of DnGreenwood
 asked on

How Do I Shade Individual Cells in Traffic Light Colors

I have a list of items in a Word Table and I would like to shade the cells adjacent to them in red, yellow or green based on a rating.
Word does not seem to let you color individual cells.  ANy help would be appreciated.
Microsoft Word

Avatar of undefined
Last Comment

8/22/2022 - Mon

This code will do what you need.

Without knowing how your document is laid out or even what the ratings would be, I created a very basic macro.  You likely will need to modify this.  It should get you to where you need, though.
Public Sub TrafficLight()
Dim tbl As Table
Dim rw As Row
Set tbl = ActiveDocument.Tables(1)
For Each rw In tbl.Rows
    If IsNumeric(CStr(Left(rw.Cells(1).Range.Text, Len(rw.Cells(1).Range.Text) - 2))) Then
        Select Case Left(rw.Cells(1).Range.Text, Len(rw.Cells(1).Range.Text) - 2)
            Case 1
                rw.Cells(2).Shading.BackgroundPatternColor = wdColorRed
            Case 2
                rw.Cells(2).Shading.BackgroundPatternColor = wdColorYellow
            Case 3
                rw.Cells(2).Shading.BackgroundPatternColor = wdColorGreen
        End Select
    End If
Next rw
End Sub

Open in new window


Just to explain a little about the method to my madness...

* The conditional If that verifies that the text is numeric can be taken out.  I put it in there because I initially planned on doing a Select Case with a range of numbers (red for 1-3, yellow for 4-7, and green for 8-10).  I ended up not doing that, but I left the code in there.  If you are doing a numerical system, then you may want to have that conditional.  If it's based on just text, then you can do without it.  In any case, the conditionals will streamline the code so it only checks the value of the cell if it is numerical.  

* You may note the use of "Len(rw.Cells(1).Range.Text) - 2".  Table cells have an end-of-cell marker.  If you look at just the cell contents, you would usually end up with two extra characters at the end.  So, that's why I look at the left of the contents and drop off the last two.

* This assumes only one table (or at least the first table).  If your document has multiple tables, then this code needs some tweaking.

Thanks for your response.  I think after further examination that I will just move the tables into excel and change the cell color to indicate the level.  Thanks for your help though!
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.

View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.