find and replacing in a table, change characters to colour (1 Viewer)

OTWarrior

Registered User.
Local time
Today, 15:19
Joined
Jan 16, 2017
Messages
22
Through an Access database I am exporting data to a word document. In this particular section, it is a table being filled with data.

There is one column that needs to be coloured, that has the set values
+
-
+/-

the plus need to be green, and the minus red. I can colour the single ones, the issue is colouring the characters in "+/-", as I don't want to colour the whole cell the same.

a find/replace wouldn't help, as it would also change the single ones.

Code:
If rL.Fields(2) = "'+" Then
       oTable.Cell(PositionA, 3).Range.Font.ColorIndex = wdGreen
ElseIf rL.Fields(2) = "'-" Then
        oTable.Cell(PositionA, 3).Range.Font.ColorIndex = wdRed
End If


I thought I would ask on here as people would be more likely to know about word vba. Any ideas?
 

Minty

AWF VIP
Local time
Today, 15:19
Joined
Jul 26, 2013
Messages
9,364
What about searching for the / ?
It's unique to the +/-
 

CJ_London

Super Moderator
Staff member
Local time
Today, 15:19
Joined
Feb 19, 2013
Messages
14,685
so far as I know, word uses the same html codes for formatting as Access so perhaps you use

replace(replace(myfield,"-","<font color=red>-</font>"),"+","<font color=green>+</font>")
 
Last edited:

OTWarrior

Registered User.
Local time
Today, 15:19
Joined
Jan 16, 2017
Messages
22
so far as I know, word uses the same html codes for formatting as Access so perhaps you use

replace(replace(myfield,"-","<font color=red>-</font>"),"+","<font color=green>+</font>")

This may be possible, could it be done within a set part of the document only ( IE, on page 3 or within a table only)?
 

CJ_London

Super Moderator
Staff member
Local time
Today, 15:19
Joined
Feb 19, 2013
Messages
14,685
you haven't said which version of word you are using, I'm assuming it is 2007 or later

your example is processing a specific column in a specific table after it has been imported so it could work there.

something like

oTable.Cell(PositionA, 3)=replace(replace(oTable.Cell(PositionA, 3),"-","<font color=red>-</font>"),"+","<font color=green>+</font>")

no need for your if statement then
 

OTWarrior

Registered User.
Local time
Today, 15:19
Joined
Jan 16, 2017
Messages
22
you haven't said which version of word you are using, I'm assuming it is 2007 or later

your example is processing a specific column in a specific table after it has been imported so it could work there.

something like

oTable.Cell(PositionA, 3)=replace(replace(oTable.Cell(PositionA, 3),"-","<font color=red>-</font>"),"+","<font color=green>+</font>")

no need for your if statement then

Sorry, Access and Word 2016.

Access doesn't like the second instance of "Replace" in the statement, it gives an error of "Type mismatch" when compiling. (I have the Word 16 object library in references)
 

CJ_London

Super Moderator
Staff member
Local time
Today, 15:19
Joined
Feb 19, 2013
Messages
14,685
you'll need to provide the exact code you have used - type mismatch implies you are missing a quotation, comma or bracket.
 

OTWarrior

Registered User.
Local time
Today, 15:19
Joined
Jan 16, 2017
Messages
22
I managed to do this in a different way. in code I select the entire table, then do a find/replace on that. There shouldn't be any of these characters in the table, but there may be elsewhere in the document

oTable.Select

WordObj.Selection.Find.Replacement.Font.Color = -651551261
With WordObj.Selection.Find
.Text = "+"
.Replacement.Text = "+"
End With
WordObj.Selection.Find.Execute Replace:=wdReplaceAll

oTable.Select
WordObj.Selection.Find.Replacement.Font.Color = vbRed
With WordObj.Selection.Find
.Text = "-"
.Replacement.Text = "-"
End With
WordObj.Selection.Find.Execute Replace:=wdReplaceAll

The second selection of the table is probably not needed, but thought I would put it there on the safe side.
 

OTWarrior

Registered User.
Local time
Today, 15:19
Joined
Jan 16, 2017
Messages
22
you'll need to provide the exact code you have used - type mismatch implies you are missing a quotation, comma or bracket.

I was using your code:

Code:
oTable.Cell(PositionA, 3)=replace(replace(oTable.Cell(PositionA, 3),"-","<font color=red>-</font>"),"+","<font color=green>+</font>")
 

CJ_London

Super Moderator
Staff member
Local time
Today, 15:19
Joined
Feb 19, 2013
Messages
14,685
I don't see anything wrong with my code, but then I have nothing to test it against. But at least you have a solution
 

Users who are viewing this thread

Top Bottom