Macro to highlight alternating sections of a table

Jennifer Murphy · #1 02-19-2020, 11:15 AM

Quote:

Originally Posted by macropod

They're listed under the wdColorIndex enumeration in the VBA Help file. You could use BackgroundPatternColor instead of BackgroundPatternColorIndex, and specify the RGB values.

Got it, thanks.

Now, is there a way to pass parameters to the macro? My search suggests that there is not. I have to have the macro ask for parameters using InputBox or a Form.

A couple of parameters that would make this macro more general are:

The number of the column where the text is
Whether the table has a header row or not
The highlight color(s)

Thanks

Jennifer Murphy · #2 02-19-2020, 12:00 PM

I tweaked the code a bit. Here is a working macro. The target text must be in column 1 and the first row is assumed to be a header.

Code:

Sub TblHiLite()

Const MyName As String = "TblHiLite"
'Const Blue As Long = 15849926   'Light blue (198,217,241)
Const Blue As Long = 15853019   'Light blue (219,229,241)
Const White As Long = 16777215  'No highlighting

'Abort if the cursor is not in a table
If Selection.Information(wdWithInTable) <> True Then
  MsgBox "The cursor is not in a table", vbOKOnly, MyName
  Exit Sub
End If

Application.ScreenUpdating = False
Dim Row As Long           'Row number (loop index)
Dim HiLiteColor As Long   'The ?????
Dim TgtTextNew As String  'The target text string
Dim TgtTextOld As String  'The previous target text string

HiLiteColor = Blue                'Initialize color
With Selection.Tables(1)          'Focus the selection on the table
  For Row = 2 To .Rows.Count         'Loop through all row but the header
    TgtTextNew = Split(.Cell(Row, 1).Range.Text, vbCr)(0) 'Get next text string
    If TgtTextNew <> TgtTextOld Then  'If it's a new section
      TgtTextOld = TgtTextNew           'Save the text
                                        'Switch colors
      If HiLiteColor = White Then HiLiteColor = Blue Else HiLiteColor = White
    End If
    .Rows(Row).Shading.BackgroundPatternColor = HiLiteColor 'Apply highlighting
  Next Row
End With
  
Application.ScreenUpdating = True

End Sub

macropod · #3 02-19-2020, 02:29 PM

Quote:

Originally Posted by Jennifer Murphy

Now, is there a way to pass parameters to the macro? My search suggests that there is not. I have to have the macro ask for parameters using InputBox or a Form.

A couple of parameters that would make this macro more general are:

The number of the column where the text is
Whether the table has a header row or not
The highlight color(s)

Passing parameters is quite easy. The usual way is to use code like:
Sub Macro(Parameter1 As Long, Parameter2 As String, Optional Parameter3 As Boolean)
where:
• 'Parameter' 1-3 are the parameter names (more than3 are allowed);
• Long, String & Boolean are the parameter types; and
• Optional indicates that the parameter need not be supplied (these must only come after all the mandatory parameters).
Thus, you might use something along the lines of:

Code:

Sub TblHiLite(ColNum As Long, Hdr As Boolean, Shading As String)
'Abort if the cursor is not in a table
If Selection.Information(wdWithInTable) = False Then
  MsgBox "The selection is not in a table!", vbOKOnly, "TblHiLite"
  Exit Sub
End If
Application.ScreenUpdating = False
Dim h As Long, n As Long, r As Long, s As Long, StrTitle As String
Const w As Long = RGB(255, 255, 255)
'Determine the start row, according to whether there's a header
If Hdr = True Then
  n = 3
Else
  n = 2
End If
'Get the applicable colour constant
Select Case Trim(LCase(Shading))
  Case "pale blue": s = RGB(198, 217, 241)
  Case "pale green": s = RGB(153, 255, 153)
  Case "pale yellow": s = RGB(255, 255, 153)
  Case "pink": s = RGB(255, 153, 153)
  Case Else: s = w
End Select
'process the table
With Selection.Tables(1)
  StrTitle = Split(.Cell(n - 1, ColNum).Range.Text, vbCr)(0)
  .Rows(2).Shading.BackgroundPatternColorIndex = 0
  h = w
  For r = n To .Rows.Count
    If Split(.Cell(r, ColNum).Range.Text, vbCr)(0) <> StrTitle Then
      If h = w Then h = s Else h = w
      StrTitle = Split(.Cell(r, ColNum).Range.Text, vbCr)(0)
    End If
    .Rows(r).Shading.BackgroundPatternColor = h
  Next
End With
Application.ScreenUpdating = True
End Sub

Jennifer Murphy · #4 02-19-2020, 03:12 PM

Quote:

Originally Posted by macropod

Passing parameters is quite easy. The usual way is to use code like:
Sub Macro(Parameter1 As Long, Parameter2 As String, Optional Parameter3 As Boolean)
where:
• 'Parameter' 1-3 are the parameter names (more than3 are allowed);
• Long, String & Boolean are the parameter types; and
• Optional indicates that the parameter need not be supplied (these must only come after all the mandatory parameters).

I guess I wasn't clear. I do know how to pass parameters to functions. I have quite a few in operation. They use a syntax like you describe.

But I don't know how to pass them to the Sub. I call my sub using a keyboard shortcut (Alt+Ctrl+Shift+h). Do I have to edit that assignment every time I want to change the parameter values to be passed?

Does that make sense?

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Macro to highlight a list of words	bakerkr	Word VBA	4	10-19-2017 02:23 PM
Word 2010 VBA Print Macro - Specified Sections	Benbon	Word VBA	3	03-30-2017 02:31 PM
Macro Question: Need help making a macro to highlight the first word in every sentence	LadyAna	Word	1	12-06-2014 10:39 PM
Macro to highlight words	bertietheblue	Word VBA	9	07-01-2013 12:39 PM
find - reading highlight - highlight all / highlight doesn't stick when saved	bobk544	Word	3	04-15-2009 03:31 PM