{"id":5953,"date":"2020-02-05T20:49:30","date_gmt":"2020-02-06T01:49:30","guid":{"rendered":"https:\/\/www.rushworth.us\/lisa\/?p=5953"},"modified":"2020-02-06T12:50:19","modified_gmt":"2020-02-06T17:50:19","slug":"excel-setting-a-cell-value-based-on-background-color","status":"publish","type":"post","link":"https:\/\/www.rushworth.us\/lisa\/?p=5953","title":{"rendered":"Excel \u2013 Setting a Cell Value Based on Background Color"},"content":{"rendered":"

I need to programmatically parse an Excel file where items are grouped with arbitrary group sizes. We don’t<\/em> 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.<\/p>\n

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.<\/p>\n

We need to define a \u2018name\u2019 for the function. On the \u201cFormulas\u201d tab, select \u201cName Manager\u201d.<\/p>\n

<\/p>\n

Select \u2018New\u2019<\/p>\n

<\/p>\n

Provide a name \u2013 I am using getBackgroundColor \u2013 and put the following in the \u201crefers to\u201d section: =GET.CELL(63,INDIRECT(“rc”,FALSE))<\/p>\n

<\/p>\n

Now we can use this name within the cell formula:<\/p>\n

<\/p>\n

Select the rows for your first group and change the \u201cfill color\u201d of the row.<\/p>\n

<\/p>\n

Repeat this process to colorize all of your groups \u2013 you can re-use a color as long as adjacent groups have different colors. Notice that the \u201cColorGroup\u201d values do not change when you colorize your groups.<\/p>\n

<\/p>\n

On the \u201cForumlas\u201d tab, select \u201cCalculate Now\u201d<\/p>\n

<\/p>\n

Now the colorized cells will have a non-zero value.<\/p>\n

<\/p>\n","protected":false},"excerpt":{"rendered":"

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 …<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[677],"tags":[708,710,709,691,729,731,265],"class_list":["post-5953","post","type-post","status-publish","format-standard","hentry","category-office-365","tag-excel","tag-excel-functions","tag-microsoft-excel","tag-microsoft-office","tag-microsoft-office-365","tag-ms-excel","tag-office-365"],"_links":{"self":[{"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=\/wp\/v2\/posts\/5953","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=5953"}],"version-history":[{"count":1,"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=\/wp\/v2\/posts\/5953\/revisions"}],"predecessor-version":[{"id":5962,"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=\/wp\/v2\/posts\/5953\/revisions\/5962"}],"wp:attachment":[{"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=5953"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=5953"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=5953"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}