It’s possible to apply individual cell formatting in Word consistently using a little VBA code. In effect it makes styles for cells in a way that Word itself won’t do. Reuse this code to apply the same formatting to many cells.
Cell Format Special
We’ve made two VBA functions for two different cell formatting settings; CellFormatSpecialHigh and CellFormatSpecialLow.
This code changes the background color for the current selection. It also ensures the texture and foreground colors don’t obscure the background coloring.
We’ve deliberately made this code very simple since most Word users aren’t familiar with VBA. In particular there’s no checking of the selection to make sure it’s a table cell.
Add these functions to a button on the ribbon or Quick Access Toolbar
The code text is below.
Code extensions
Here’s some extensions and improvements you might try.
Checking cell selection
As noted in the code comments, there’s no error checking. The code assumes it’s only run with a cell or cells selected. It will apply shading to whatever selection is current when the function is run.
A quick but incomplete check is possible using this code which checks if the selection is inside a table.
If Selection.Information(wdWithInTable) Then ‘ … your formatting changes End If
Single function for different background
The code could be changed so there’s a single function with a parameter passed in with the background color desired e.g. Sub CellFormatBackground( wdBlue )
Cell Styles via VBA
Or a function which, in effect, gives you individual cell styles. Accepting a name then applying cell formatting based on that name e.g. Sub CellFormatbyName( “HighValue” ) , Sub CellFormatbyName( “Special” ) .
Removing special formatting
To remove the special cell formatting use this code:
It changes the .BackgroundPatternColor to ‘Automatic’ which, in effect, means the table style setting.
VBA Code
Here’s the code samples used above.
CellFormatSpecialHigh
Sub CellFormatSpecialHigh() ' Changes the current cell to specific background color for High values ' assumes a single cell is selected .. there's no error checking!!!! With Selection.Shading ' the cell background color .BackgroundPatternColor = wdYellow ' make sure there's no Texture or foreground color setting .Texture = wdTextureNone .ForegroundPatternColor = wdColorAutomatic End With End Sub
CellFormatSpecialLow
Sub CellFormatSpecialLow() ' Changes the current cell to specific background color for Low values ' assumes a single cell is selected .. there's no error checking!!!! With Selection.Shading ' the cell background color .BackgroundPatternColor = wdGreen ' make sure there's no Texture or foreground color setting .Texture = wdTextureNone .ForegroundPatternColor = wdColorAutomatic End With End Sub
Remove cell formatting
Sub CellFormatSpecialNOT() ' Reverts the current cell to original style ' assumes a single cell is selected .. there's no error checking!!!! With Selection.Shading .BackgroundPatternColor = wdColorAutomatic .Texture = wdTextureNone .ForegroundPatternColor = wdColorAutomatic End With End Sub