Need code to change background color based on text value

[mpdsal]

Greetings.

I have a Word 2007 document that I use as an audit report template. There are two columns in different sections of the document. Both columns (table heading) name is Risk Ranking. The risks can be rated using the following text values: RED, YELLOW, GREEN. As you can imagine it would be nice if after I typed the respective text value the entire cell background color would change to correspond to that text value. See attachment.

Does anyone have any ideas they could share so that this could be performed using a macro or VB program?

I appreciate it.

Regards,

Mark

[Frosty]

I'll plug one of the regular responders here...
http://gregmaxey.mvps.org/word_tips.html
(look at the content controls samples)

There are ways to set up what you want to have happen (automatic row highlighting based on text entry) in a word table using content controls.

However, the concept you're talking about is easier in excel... and it falls under a built-in feature called "Conditional Formatting".

In a word table, it would be easier to have a macro which simply highlights rows appropriately (or goes through the entire table and checks a particular cell's text), but real-time formatting based on changing text is trickier.

But you should do a little research and decide what your comfort level is in customizing code. Greg's samples have good walk throughs to show you what is possible.

But, if it were me, I'd probably look at doing it all in Excel.

[macropod]

See the attached. It's from a similar request I dealt with ages ago. You could adapt it easily enough for your own purposes, by using formfields in your text-entry cells as well.

[fumei]

Sure sounds like an Excel job to me.

[Talis]

This assumes that the relevant table is the first table: Tables(1), so change this if necessary.

Sub ColourCells() 
    Dim tblOne As Table 
    Dim celTable As Cell 
    Dim rngTable As Range 
    Dim i As Long 
    Set tblOne = ActiveDocument.Tables(1) 
    For i = 1 To tblOne.Rows.Count 
        For Each celTable In tblOne.Rows(i).Cells 
            Set rngTable = celTable.Range 
            rngTable.MoveEnd Unit:=wdCharacter, Count:=-1 
            Select Case UCase(rngTable.Text) 
            Case "RED" 
                rngTable.Shading.BackgroundPatternColor = wdColorRed 
            Case "YELLOW" 
                rngTable.Shading.BackgroundPatternColor = wdColorYellow 
            Case "GREEN" 
                rngTable.Shading.BackgroundPatternColor = wdColorGreen 
            End Select 
        Next celTable 
    Next i 
End Sub 

VBA tags courtesy of www.thecodenet.com

[Talis]

Hm, no response.
What I would like you to consider as a temporary route is having a subroutine to which you have assigned a key combination such as Alt-Shift-C.
Also there is no need to write out the names of the colours if you use the subroutine below. Simply type H, M or L. The case doesn't matter. You have a choice of leaving the words High, Medium or Low showing or you can modify the code (by removing the three apostrophes) so that the letters take on the same colour as the background and can't be seen. I prefer this method rather making them "hidden" so that they can be selected and changed (double click on where the word is and use the A on the format toolbar).

Sub ColourCells() 
    Dim tblOne As Table 
    Dim celTable As Cell 
    Dim rngTable As Range 
    Dim i As Long 
    Set tblOne = ActiveDocument.Tables(1) 
    For i = 1 To tblOne.Rows.Count 
        For Each celTable In tblOne.Rows(i).Cells 
            Set rngTable = celTable.Range 
            rngTable.MoveEnd Unit:=wdCharacter, Count:=-1 
             'REMOVE APOSTROPHE AT START OF EACH LINE BELOW TO CONCEAL TEXT
            Select Case UCase(rngTable.Text) 
            Case "H" 
                rngTable.Shading.BackgroundPatternColor = wdColorRed 
                 'rngTable.Font.Color = wdColorRed
                rngTable.Text = vbCr & "High" 
            Case "M" 
                rngTable.Shading.BackgroundPatternColor = wdColorYellow 
                 'rngTable.Font.Color = wdColorYellow
                rngTable.Text = vbCr & "Moderate" 
            Case "L" 
                rngTable.Shading.BackgroundPatternColor = wdColorGreen 
                 'rngTable.Font.Color = wdColorGreen
                rngTable.Text = vbCr & "Low" 
            End Select 
        Next celTable 
    Next i 
End Sub 

VBA tags courtesy of www.thecodenet.com

[mpdsal]

Talis,

Forgive me. I was sidetracked away from this project to do something much less fun.

At any rate. I did utilize your first coding suggestion and it works great! I did exactly as you suggested and referenced the respective tables that I needed the risk rating color to appear and it worked like a charm.

It's funny how you suggested the alternate code you submitted on April 4. It was brought to my attention that when the risk rating is RED or GREEN the text color has to be white. For YELLOW, the default color black is fine. Could you suggest a modification to your code to change the text color as well as the background shading?

One other question. In my template I have assigned this macro to an icon in my quick access toolbar. When I send this template out to other team members will that icon and macro assignment be present on their computers as well? If not could you suggest another way to assign the macro to an icon or button to make it user friendly?

In closing I would like to thank you and other members of these user forums for taking the time to assist us less knowledgable brethren with finding solutions to our questions. I truly appreciate the assistance.

Regards,

Mark

[Talis]

The post at #6 shows you how to change font colours to what you want. Remove the apsotrophe to uncomment the line. so for RED & GREEN backgrounds you would use:

rngTable.Font.Color = wdColorWhite

and for YELLOW

rngTable.Font.Color = wdColorBlack

or it could be wdColorAutomatic.

Regarding sending out your macro and QAT button, take a look at this post by Frosty

http://www.vbaexpress.com/forum/showthread.php?t=41444

#4

and download his .docm to see what can be done. I've never done it but in general I believe you open the VBA Editor and double click ThisDocument in the Project window. This opens the code window. That's where you put the code. I know nothing about the QAT but assume that it is sent with the document. Maybe someone else could help with this. (I use Word 2003).

[mpdsal]

Thanks for the speedy reply!

It was right before my very eyes the whole time!!

I'll take a look at the docm link you sent as well.

Cheers!

[mpdsal]

Talis,

What would the syntax be, using the same or similar code noted above, except I want to test the value of a single cell in Table 1 instead of rows, to change the background and font colors of that cell?

Thanks

Mark