Convert cell styles (formatting) to values

[KrsOne20]

In my datasheet I have applied various styles to cells, each style (format) represents something as seen below. For example pink means press release (PR). However now I need to convert this data into a mastertable (which can be used to create pivots, or can be read into SPSS). The issue is that VLOOKUP (or match index) discards the formatting/styles when applied.

Is there a VBA code to convert the cell styles to values? For example data in cell A2 the code will insert 'CR', as that is the name of the style applied. Let me know! Thanks in advance


Example

Cell styles

 

[KrsOne20]

I think I found a solution online on https://www.extendoffice.com/documents/excel/4546-excel-determine-color-of-cell.html. It seems to work fine.
But if anyone still has a better solution to get CELL STYLE name instead of RGB code, its still welcome!

 

[sheetspread]

Were these colors chosen from the menu by users (i.e. there's no written "map" of conditions you can refer to)? In that case, find the color index with VBA and look that up from a table you build. If you have the criteria written out somewhere then it's even simpler.

 

[sheetspread]

get CELL STYLE name

The style property in VBA (.style) should return the name

 

[KrsOne20]

The style property in VBA (.style) should return the name

Thanks for your response. How can I adjust this formula below, so that when I enter the formula =A1 in cell A2, I get the style name in cell A2? (sorry Im a noob at creating VBA codes)

<code class="vb keyword">Function</code> <code class="vb plain">getRGB1(FCell </code><code class="vb keyword">As</code> <code class="vb plain">Range) </code><code class="vb keyword">As</code> <code class="vb keyword">String</code>

<code class="vb comments">'UpdatebyExtendoffice20170714</code>

<code class="vb spaces">    </code><code class="vb keyword">Dim</code> <code class="vb plain">xColor </code><code class="vb keyword">As</code> <code class="vb keyword">String</code>

<code class="vb spaces">    </code><code class="vb plain">xColor = </code><code class="vb keyword">CStr</code><code class="vb plain">(FCell.Interior.Color)</code>
<code class="vb spaces">    </code><code class="vb plain">xColor = Right(</code><code class="vb string">"000000"</code> <code class="vb plain">& Hex(xColor), 6)</code>

<code class="vb spaces">    </code><code class="vb plain">getRGB1 = Right(xColor, 2) & Mid(xColor, 3, 2) & Left(xColor, 2)</code>
<code class="vb keyword">End</code> <code class="vb keyword">Function</code>

 

[sheetspread]

Something like:

Function getstyle(fcell As Range) As String
getstyle = fcell.Style
End Function

 

[KrsOne20]

Something like:

Function getstyle(fcell As Range) As String
getstyle = fcell.Style
End Function

Hmm, I'm getting a error. It should show style 'IGD'. I indeed used the styles consistently, so it should show 'IGD'. However not a big deal, issue is also solved with RGB code. Thanks anyways for your input!

 

[KrsOne20]

Something like:

Function getstyle(fcell As Range) As String
getstyle = fcell.Style
End Function

Update on the above, the code does work on new projects in xlsx, but not on my existing project in xlsm

 

[sheetspread]

You saved to a module and not a worksheet, right? Does this error persist even after rebooting?

 

[KrsOne20]

You saved to a module and not a worksheet, right? Does this error persist even after rebooting?

It was already in a module. However I just re-added the code and it works! Weird that it does work now. I think the issue was the name of the module (earlier I renamed the module to getStyle).