How to Customize Colors on a CreateCustomTable R Table

Introduction

This article describes how to change colors on a table based on values in a column. In our example we will go from an R table with a column of differences...

...to a CreateCustomTable R table (similar to a table with Autofit checked) with background color formatting using the FlipFormat package:

...and also a CreateCustomTable R table with custom font colors:

Requirements

• An R table with time periods in the columns and a final column of differences between the latest time periods as per the final step in How to Calculate the Difference between Two Columns using R.
• Knowledge of customizations available in CreateCustomTable.

Method - Customizing Background Colors

1. Select your R table made by a custom Calculation.

2. Below the existing R code in the R CODE box paste the following:

tab = diff.table # reference the table you'd like to format
tab = tab[-NROW(tab),] # remove last row
#make matrix of cell.colors the same shape as your table default the color to white
cell.colors=matrix("white",nrow=NROW(tab),ncol=NCOL(tab))
#shade the cells green above 0%
cell.colors[tab[,NCOL(tab)] > 0,NCOL(tab)]="green"
#shade cells red below 0%
cell.colors[tab[,NCOL(tab)] < 0,NCOL(tab)]="red"
#create custom table
flipFormat::CreateCustomTable(tab,
row.header.fill = "#636363",
row.header.font.color = "white",
row.header.pad = 5,
col.header.fill = "#636363",
col.header.font.color = "white",
corner.fill = "#636363",
corner.font.color = "white",
cell.fill=cell.colors)

This code first removes the NET row, then it creates a matrix of colors with the same dimensions as the source table. Next, we add conditions to this matrix to change the cells in the last column green or red based on the cell value. Finally, we use the CreateCustomTable function to create our AutoFit style table by setting background colors, padding, font color and define the cell colors based on our color matrix.

Method - Customizing Font Colors

1. Select your R table made by a custom Calculation.

2. Below the existing R code in the R CODE box paste the following:

tab = diff.table # reference the source table
tab = tab[-NROW(tab),] # remove last row
#make matrix of font.colors the same shape as your table
#default the font color to black
font.colors=matrix("black",nrow=NROW(tab),ncol=NCOL(tab))
#make font green above 0%
font.colors[tab[,NCOL(tab)] > 0,NCOL(tab)]="green"
#make font red below 0%
font.colors[tab[,NCOL(tab)] < 0,NCOL(tab)]="red"
#create custom table
flipFormat::CreateCustomTable(tab,
            row.header.fill = "#636363",
            row.header.font.color = "white",
            row.header.pad = 5,
            col.header.fill = "#636363",
            col.header.font.color = "white",
            corner.fill = "#636363",
            corner.font.color = "white",
            cell.font.color=font.colors)

This code first removes the NET row, then it creates a matrix of font colors with the same dimensions as the source table. Next, we add conditions to this matrix to change the font colors in the last column green or red based on the cell value. Finally, we use the CreateCustomTable function to create our AutoFit style table by setting background colors, padding, and font colors. We define the font colors based on our color matrix on the last line of code.

See Also

How to Calculate the Difference between Two Columns using R

CreateCustomTable

How to Create an AutoFit Table