Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 09-11-2019, 06:45 AM
KateWord KateWord is offline How to run colour conditional formatting in just column, not whole table Windows 7 64bit How to run colour conditional formatting in just column, not whole table Office 2007
Novice
How to run colour conditional formatting in just column, not whole table
 
Join Date: Sep 2019
Posts: 1
KateWord is on a distinguished road
Default How to run colour conditional formatting in just column, not whole table

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
Reply With Quote
  #2  
Old 09-11-2019, 11:03 PM
gmayor's Avatar
gmayor gmayor is offline How to run colour conditional formatting in just column, not whole table Windows 10 How to run colour conditional formatting in just column, not whole table Office 2016
Expert
 
Join Date: Aug 2014
Posts: 4,101
gmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud of
Default

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
__________________
Graham Mayor - MS MVP (Word) (2002-2019)
Visit my web site for more programming tips and ready made processes www.gmayor.com
Reply With Quote
  #3  
Old 09-12-2019, 05:30 AM
Charles Kenyon Charles Kenyon is offline How to run colour conditional formatting in just column, not whole table Windows 10 How to run colour conditional formatting in just column, not whole table Office 2016
Moderator
 
Join Date: Mar 2012
Location: Sun Prairie, Wisconsin
Posts: 9,081
Charles Kenyon has a brilliant futureCharles Kenyon has a brilliant futureCharles Kenyon has a brilliant futureCharles Kenyon has a brilliant futureCharles Kenyon has a brilliant futureCharles Kenyon has a brilliant futureCharles Kenyon has a brilliant futureCharles Kenyon has a brilliant futureCharles Kenyon has a brilliant futureCharles Kenyon has a brilliant futureCharles Kenyon has a brilliant future
Default

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.
Reply With Quote
Reply

Tags
colour, formatting cells from vba, vba

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
How to run colour conditional formatting in just column, not whole table 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
How to run colour conditional formatting in just column, not whole table 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

Other Forums: Access Forums

All times are GMT -7. The time now is 06:55 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft