How to conditional format a table cell in word based on it's value.

JulianS96 · #1 10-18-2021, 05:41 AM

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

Guessed · #2 10-18-2021, 02:45 PM

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.

JulianS96 · #3 10-18-2021, 10:45 PM

Quote:

Originally Posted by Guessed

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.

Ah ok cheers! I'll post one when I get into work in an hour!

JulianS96 · #4 10-19-2021, 12:41 AM

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

gmayor · #5 10-19-2021, 02:51 AM

It works more readily if you use a content control and its on exit event - see attached

JulianS96 · #6 10-19-2021, 03:33 AM

Quote:

Originally Posted by gmayor

It works more readily if you use a content control and its on exit event - see attached

Hi Graham,

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

gmayor · #7 10-19-2021, 04:50 AM

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.

JulianS96 · #8 10-19-2021, 05:22 AM

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

Guessed · #9 10-19-2021, 03:31 PM

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

JulianS96 · #10 10-20-2021, 02:25 AM

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