excel :: generate text based on cell colour

I work with a lot of spreadsheets. A lot. Every time I look at them and think “this could have been presented better”.
Recently I received a spreadsheet that contained three different types of data in it, but the data was jumbled, and the only way to differentiate was by cell colour. Strange, I know. I needed a filter column that had each type so I could easily extrapolate the data, so I whipped up a quick macro.
So it’s quite easy, the hardest part is working out what the colour code used is. There’s a neat trick.

First, highlight the cell with the colour.
Then click on the paint bucket drop down.
Click “More Colours” and it will open a pallet.
The pallet will have auto selected the colour.
Click “record macro” in the developer toolbar, and click the paint bucket tool.
That’s it, check in the developer code your recorded code, and it will have the colour code there.
Easy as pie.

Then here’s the code, obviously adjust it based on your own requirements.

Sub ColourBasedOnCells()
i = 2
Row = 1
While Cells(Row, 1) <> ""
    Select Case Cells(Row, 1).Interior.Color
      Case 49407
        Cells(Row, 13) = "STP"
    Case 5296274
        Cells(Row, 13) = "Online"
    Case 14857357
        Cells(Row, 13) = "Batch"
    Case Else
        Cells(Row, 13) = "Unknown"
    End Select
    Row = Row + 1
End Sub

And then just hit run.


Leave a Reply

Your email address will not be published. Required fields are marked *


You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>