I need to programmatically parse an Excel file where items are grouped with arbitrary group sizes. We don’t want the person filling out the spreadsheet to need to fill in a group # column … so I’m exploring ways to read cell formatting so something like color can be used to show the groups. Reading the formatting isn’t a straight-forward process, so I wondered if Excel could populate a group number cell based on the cell’s attributes.

While it is possible, it’s not a viable solution. The mechanism to access data about a cell cannot be accessed directly and, unfortunately, requires a macro-enabled workbook. The mechanism also requires the user to remember to update the spreadsheet calculations when they have finished colorizing the rows. While I won’t be using this approach in my current project … I thought I’d record what I did for future reference.

We need to define a ‘name’ for the function. On the “Formulas” tab, select “Name Manager”.

Select ‘New’

Provide a name – I am using getBackgroundColor – and put the following in the “refers to” section: =GET.CELL(63,INDIRECT(“rc”,FALSE))

Now we can use this name within the cell formula:

Select the rows for your first group and change the “fill color” of the row.

Repeat this process to colorize all of your groups – you can re-use a color as long as adjacent groups have different colors. Notice that the “ColorGroup” values do not change when you colorize your groups.

On the “Forumlas” tab, select “Calculate Now”

Now the colorized cells will have a non-zero value.