Change Text Colour in Cell Based on Text in Same Cell

PMC11 · #1 11-14-2017, 01:05 PM

Hi,

I am new to VBA so I am struggling with what seems to be quite a simple task.

I have a few lines of text in each cell in a word document. Each cell contains a category such "Science" or "Health" or one of several others. At the minute I'm actually just using a special character such as "*" or "@" for testing purposes.

I need the text colour of all text in the cell to change depending on which category is in the cell. So the text would be e.g. green for "Science" and red for "Health".

It seems that running a macro is quickest way of making these changes (there will be over 200 such cells in my final document and colouring manually is such a waste of time). Basically, I'm struggling with first changing the colour of all the text in the cell, and secondly how to make the macro search again if the first criteria is not met. I would like 1 macro that could complete colouring for the entire document, rather than having multiple macros for each colour I need.

If you could give me some examples of VBA I could work with that would be most helpful. I'm really struggling and any help you could give will save me and my team so much time.

gmayor · #2 11-14-2017, 09:15 PM

Maybe something like the following. Note the search is case sensitive.

Code:

Sub Macro1()
Dim oTable As Table
Dim oCell As Range
Dim i As Long
    For Each oTable In ActiveDocument.Tables
        For i = 1 To oTable.Range.Cells.Count
            Select Case True
                Case InStr(1, oTable.Range.Cells(i).Range.Text, "Science") > 0
                    Set oCell = oTable.Range.Cells(i).Range
                    oCell.End = oCell.End - 1
                    oCell.Font.Color = wdColorGreen
                Case InStr(1, oTable.Range.Cells(i).Range.Text, "Health") > 0
                    Set oCell = oTable.Range.Cells(i).Range
                    oCell.End = oCell.End - 1
                    oCell.Font.Color = wdColorRed
                    'etc
            End Select
        Next i
    Next oTable
lbl_Exit:
    Set oTable = Nothing
    Set oCell = Nothing
    Exit Sub
End Sub

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
count the number of text in a cell based off a different cells text	Kubi	Excel	4	08-24-2017 05:53 PM
VBA to immediately change the colour of a cell depending on the code placed in anothe	Phil Payne	Excel Programming	2	07-27-2013 11:04 PM
Quadrant colour based on cell value	RoyLittle0	Excel	2	05-05-2013 12:50 AM
Change formula cell range based on cell value	Scoth	Excel	4	10-25-2012 07:51 AM
CHange colour of footer if a cell changes to red	OTPM	Excel	0	05-26-2011 07:15 AM