Consulting

Results 1 to 10 of 10

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

  1. #1
    VBAX Regular
    Joined
    Apr 2012
    Posts
    17
    Location

    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

  2. #2
    VBAX Master
    Joined
    Feb 2011
    Posts
    1,480
    Location
    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
    Knowledge Base Approver VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    4,435
    Location
    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
    Cheers
    Paul Edstein
    [Fmr MS MVP - Word]

  4. #4
    VBAX Wizard
    Joined
    May 2004
    Posts
    6,713
    Location
    Sure sounds like an Excel job to me.

  5. #5
    VBAX Regular
    Joined
    Jan 2011
    Posts
    82
    Location
    This assumes that the relevant table is the first table: Tables(1), so change this if necessary.
    [VBA]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]

  6. #6
    VBAX Regular
    Joined
    Jan 2011
    Posts
    82
    Location
    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).
    [VBA]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]

  7. #7
    VBAX Regular
    Joined
    Apr 2012
    Posts
    17
    Location
    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
    VBAX Regular
    Joined
    Jan 2011
    Posts
    82
    Location
    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).

  9. #9
    VBAX Regular
    Joined
    Apr 2012
    Posts
    17
    Location
    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
    VBAX Regular
    Joined
    Apr 2012
    Posts
    17
    Location
    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
  •