#1
|
|||
|
|||
How to conditional format a table cell in word based on it's value.
Hi Forum,
I'm new to VBA but slowly learning it. I've created a script which runs through 1K pages and deletes content not needed (down to ~18 pages). What I need to do now is add a conditional formatting script to the end to format words in the "Effectiveness" Table where if it finds: "Weak" highlight table cell Orange, Effective - Blue, Not Applicable - Purple, Adequate - Green. Is there a script that can automatically format these words in the table cells? Kind Regards, Julian |
#2
|
||||
|
||||
A macro to do this is relatively simple but you probably need to post a sample document to show us:
1. How to identify the 'Effectiveness' table 2. Where the cells are and what else is in those cells eg spaces, paragraph marks etc.
__________________
Andrew Lockton Chrysalis Design, Melbourne Australia |
#3
|
|||
|
|||
Ah ok cheers! I'll post one when I get into work in an hour!
|
#4
|
|||
|
|||
Hi Guessed,
Please find attached my mockup document. I have desensitised the document and shown where the effectiveness Tables are by highlighting the first columns of the tables in blue. The Part that I want to change colour depending on it's Value is the second cell in the first row. Weak = Orange (White font) Effective = Blue (White font) Adequate = Green (White font) Not Applicable = Purple (Black font) Regards, Julian |
#5
|
||||
|
||||
It works more readily if you use a content control and its on exit event - see attached
__________________
Graham Mayor - MS MVP (Word) (2002-2019) Visit my web site for more programming tips and ready made processes www.gmayor.com |
#6
|
|||
|
|||
Quote:
Although a content control is a nice edition, I would like this to be a seamless and quick change, whereby if the word is in the box, the colour will match the word. If the content requires having content control is there a script which can automatically make all the cells that are required content control enabled AND THEN the colour can be changed automatically with a script? Thanks in advance, Julian |
#7
|
||||
|
||||
There is no event associated with the change of value in a table cell that will format the cell colour automatically in Word.
Using a content control to select the value, the on exit event of that control can be used, as in the example, to format according to the value when you tab out of the control. The same event can be used with multiple controls as required. The other less attractive option relies on the correct values to be entered in the table cell(s) and then subsequently run a macro to format the cell(s) based on the value(s) entered.This is hardly seamless.
__________________
Graham Mayor - MS MVP (Word) (2002-2019) Visit my web site for more programming tips and ready made processes www.gmayor.com |
#8
|
|||
|
|||
Hi Graham,
I appreciate your help. I think I may have not made clear that the values that are in the cells I want to auto-colourise are from a direct export from another program. These values will stay as is and I have not modified these and they will not be modified in the future. If you already knew what I meant, then sorry, I don't mean to patronise you. Based on this, is there a way to have a script look for: "Weak" If "Weak" is found, colour cell "Orange". (white font) "Effective" If "Effective" is found, colour cell Blue". (white font) "Adequate" If "Adequate" is found, colour cell "Green". (white font) "Not Applicable" If "Not Applicable" is found, colour cell "Purple". (black font) Font colour change is not a necessity and if this over complicates the task then feel free to leave it out. Regards, Julian |
#9
|
||||
|
||||
This is the basic code I would start with to match with your sample doc. You can adjust the colours and add a font handler if you need it but the automatic font colour you already have might be adequate for that.
Code:
Sub TrafficLights() Dim aTbl As Table, aCell As Cell, sEff As String, sC2 As String For Each aTbl In ActiveDocument.Tables sEff = Trim(Split(aTbl.Cell(1, 1).Range.Text, vbCr)(0)) If sEff = "Effectiveness" Then Set aCell = aTbl.Cell(1, 1).Next sC2 = aCell.Range.Text sC2 = Trim(Split(sC2, vbCr)(0)) Select Case sC2 Case "Weak" aCell.Shading.ForegroundPatternColor = wdColorLightOrange Case "Effective" aCell.Shading.ForegroundPatternColor = wdColorBlue Case "Adequate" aCell.Shading.ForegroundPatternColor = wdColorGreen Case "Not Applicable" aCell.Shading.ForegroundPatternColor = wdColorIndigo Case Else aCell.Shading.ForegroundPatternColor = wdColorWhite End Select End If Next aTbl End Sub
__________________
Andrew Lockton Chrysalis Design, Melbourne Australia |
#10
|
|||
|
|||
Guessed that is fantastic!
Thanks for your response the code works perfectly without a hitch! Amazing how it's done the font colour correctly as well! Knew it would be a fairly simple code, I just couldn't work it out by myself! Cheers! |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Cell fill based on conditional formatting | allex011 | Excel | 7 | 07-07-2021 10:30 AM |
Conditional format a button based off a value in a field | Triscia | Misc | 0 | 03-11-2015 11:12 AM |
Conditional Formatting based on Value of Another Cell | SteveBump | Excel | 3 | 03-07-2014 09:16 AM |
How can I count cells based on their fill color that was set by conditional format | trueimage | Excel Programming | 1 | 10-25-2013 12:08 PM |
Table with Conditional Format to Word | billyV | Excel | 9 | 06-07-2013 08:26 AM |