How to run colour conditional formatting in just column, not whole table

KateWord · #1 09-11-2019, 06:45 AM

Hi,

I have created a table, where I want one column to be colour coded based on their grade. When I run the macro though, it runs on the whole table rather than selected column.

Is there a way to get it to just run on selected column, or is there some code I could put in that would target the column based on a word included in the title, and then format that column only? Sure its something simple, but I'm new to this!

Dim c As Word.Cell
If Selection.Information(wdWithInTable) Then
For Each c In Selection.Tables(1).Range.Cells
If IsNumeric(Left(c.Range.Text, Len(c.Range.Text) - 1)) Then
If Val(c.Range.Text) = 1 Then
c.Shading.BackgroundPatternColor = 10669990
ElseIf Val(c.Range.Text) = 2 Then
c.Shading.BackgroundPatternColor = 12443072
ElseIf Val(c.Range.Text) = 3 Then
c.Shading.BackgroundPatternColor = 10546144
ElseIf Val(c.Range.Text) = 4 Then

c.Shading.BackgroundPatternColor = 12512766
ElseIf Val(c.Range.Text) = 5 Then
c.Shading.BackgroundPatternColor = 12440539
ElseIf Val(c.Range.Text) = 6 Then
c.Shading.BackgroundPatternColor = 9813759
ElseIf Val(c.Range.Text) = 7 Then
c.Shading.BackgroundPatternColor = 12233699
ElseIf Val(c.Range.Text) = 8 Then
c.Shading.BackgroundPatternColor = 12830711
ElseIf Val(c.Range.Text) = 9 Then
c.Shading.BackgroundPatternColor = 12830711
ElseIf Val(c.Range.Text) < 9 Then
c.Shading.BackgroundPatternColor = wdColorWhite

Else
c.Shading.BackgroundPatternColor = wdColorWhite
End If
Else ' set non-numeric to White
c.Shading.BackgroundPatternColor = wdColorWhite
End If
Next
End If

End Sub

gmayor · #2 09-11-2019, 11:03 PM

Assuming that your table does not have merged or split cells and there is a header row, then the following macro will look for the column header 'Score' (change as appropriate) then will use your process to format only that column:

Code:

Sub Macro1()
Dim oTable As Table
Dim c As Word.Cell
Dim iCol As Integer
Set oTable = Selection.Tables(1)    'or ActiveDocument.Tables(1)
    For iCol = 1 To oTable.Columns.Count
        If InStr(1, oTable.Rows(1).Cells(iCol), "Score") > 0 Then
            For Each c In oTable.Range.Columns(iCol).Cells
                If IsNumeric(Left(c.Range.Text, Len(c.Range.Text) - 1)) Then
                    If Val(c.Range.Text) = 1 Then
                        c.Shading.BackgroundPatternColor = 10669990
                    ElseIf Val(c.Range.Text) = 2 Then
                        c.Shading.BackgroundPatternColor = 12443072
                    ElseIf Val(c.Range.Text) = 3 Then
                        c.Shading.BackgroundPatternColor = 10546144
                    ElseIf Val(c.Range.Text) = 4 Then
                        c.Shading.BackgroundPatternColor = 12512766
                    ElseIf Val(c.Range.Text) = 5 Then
                        c.Shading.BackgroundPatternColor = 12440539
                    ElseIf Val(c.Range.Text) = 6 Then
                        c.Shading.BackgroundPatternColor = 9813759
                    ElseIf Val(c.Range.Text) = 7 Then
                        c.Shading.BackgroundPatternColor = 12233699
                    ElseIf Val(c.Range.Text) = 8 Then
                        c.Shading.BackgroundPatternColor = 12830711
                    ElseIf Val(c.Range.Text) = 9 Then
                        c.Shading.BackgroundPatternColor = 12830711
                    ElseIf Val(c.Range.Text) < 9 Then
                        c.Shading.BackgroundPatternColor = wdColorWhite

                    Else
                        c.Shading.BackgroundPatternColor = wdColorWhite
                    End If
                Else    ' set non-numeric to White
                    c.Shading.BackgroundPatternColor = wdColorWhite
                End If
            Next c
        End If
    Next iCol
    Set oTable = Nothing
    Set c = Nothing
End Sub

Charles Kenyon · #3 09-12-2019, 05:30 AM

Graham has provided a good solution, as usual.

Note that conditional formatting is far easier to achieve using Excel. You may want to use that for this part.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Conditional Formatting that highlights cells when there was a change in another column	Marcia	Excel	4	04-28-2019 07:34 AM
How to fill a cell at row and column intersection with conditional Formatting	Iraj	Excel	10	12-02-2017 09:54 AM
Conditional Formatting a column	Washbue1	Excel	3	01-27-2017 01:11 PM
Conditional Formatting not expanding along with the table expanded	tinfanide	Excel	3	10-07-2014 10:00 AM
Conditional Formatting: How to dynamically apply to an expanding table?	tinfanide	Excel	0	10-18-2013 06:34 AM