Microsoft Excel Webinar

Results 1 to 10 of 10

Thread: Need code to change background color based on text value

  1. #1

    Need code to change background color based on text value

    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
    Attached Files Attached Files
      To view attachments your post count must be 0 or greater. Your post count is 0 momentarily.

  2. #2
    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.

  3. #3
    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.
    Attached Files Attached Files
      To view attachments your post count must be 0 or greater. Your post count is 0 momentarily.
    Cheers
    Paul Edstein
    [MS MVP - Word]

  4. #4
    Sure sounds like an Excel job to me.

  5. #5
    This assumes that the relevant table is the first table: Tables(1), so change this if necessary.
    VB:
    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 
    
    
    Formatting tags added by mark007

  6. #6
    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).
    VB:
    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 
    
    
    Formatting tags added by mark007

  7. #7
    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

  8. #8
    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:
    VB:
    rngTable.Font.Color = wdColorWhite 
    
    
    Formatting tags added by mark007
    and for YELLOW
    VB:
    rngTable.Font.Color = wdColorBlack 
    
    
    Formatting tags added by mark007
    or it could be wdColorAutomatic.

    Regarding sending out your macro and QAT button, take a look at this post by Frosty
    VB:
    http://www.vbaexpress.com/forum/showthread.php?t=41444 
    
    
    Formatting tags added by mark007
    VB:
    #4 
    
    
    Formatting tags added by mark007
    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).

  9. #9
    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!

  10. #10
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •