Converting coloured text cell entries into integer values

[Kristyna_sea]

Hello, I have what sounds like a fairly simple problem which I hope somebody wise in Excel might find interesting and be able to answer. To me it seems like I want the reverse of conditional colour formatting...?

I have rows in a table of wildlife observations data, which are entered in one of 3 different colours (black, blue, purple). Each colour represents data collected by a different wildlife observer. I would like to create a column adjacent to the data which will tell me which observer collected that row's data. E.g. black rows would give a cell value of 1, blue rows a value of 2, and purple rows a value of 3. Extension: instead of returning integer values, I would also like to return text strings in an additional column, for example, the observer's name. The desired columns I would like to create are coloured red in the example below.

Species Number Integer Observer Name
Grey seal 6 1 Tim
Puffin 8 1 Tim
Killer whale 2 2 Jessica
Arctic tern 40 2 Jessica
Pilot whale 7 2 Jessica
Cormorant 4 3 Jonathan
Kittiwake 10 3 Jonathan
Razorbill 3 3 Jonathan

It's my first time on this forum, any help greatly appreciated. Thanks in advance!

 

[NARAYANK991]

Hi Kristyna ,

From where can the names be retrieved ? Is there any table where you have data that entries in black have been entered by Tim , entries in blue have been entered by Jessica ,... ?

Since Excel formulae cannot recognize the colour of a cell , is VBA acceptable ?

Narayan

 

[Kristyna_sea]

Hi Narayan,

Thanks for responding so quickly. I don't currently have a table with that information, but could easily make one up. I have a very basic (but learning!) knowledge of VBA so am keen to try that if Excel formulae are a no-go.

Kristyna

 

[NARAYANK991]

Hi Kristyna ,

If the VBA code is posted here , can you copy it into your working file and make it work ?

If not , upload your workbook , and we can put the code in it , test it , and thereafter re-upload it.

Narayan

 

[Kristyna_sea]

Hi Narayan,

I should be okay to put the VBA code into my workbook - can try that first?

Thanks.
Christina

 

[NARAYANK991]

Hi Christina ,

Can you check the uploaded file ?

I have defined a named range Colour_vs_Observer_Table to refer to the table of colours vs. observers.

Narayan

 

[Somendra Misra]

Hi Christina,

If the integer are not dedicated and you can assign them different number, than please check the uploaded file.
I had use a UDF to return colour index (which is an integer value) of the cell font than use a simple vlookup to get observer name.

See if this is of any help.

Regards!

 

[Kristyna_sea]

Hi Narayan and Somendra,

Many thanks for both suggestions. They both seem to work very well, and I can see each have their own advantages.

In Narayan's solution, I am interested to know how to set the colour variables in RGB format - how can I find out the RGB combination for a text colour?

I am going to try each solution on my larger dataset and will let you know how I get on.

Extremely grateful for your help, and hope this will be useful to others.

Thank you!

Christina

 

[Abhijeet R. Joshi]

@NARAYANK991 : Won't a text delimiter help?

 

[Kristyna_sea]

Hi again, I've applied Somendra's solution to a larger dataset and got it to work - now I can go to my Christmas Party this afternoon feeling good!

I will try Narayan's once I know how to find out the RGB combinations.

Thanks again all.

 

[NARAYANK991]

Hi Christina ,

You do not need to know the RGB combinations ; I put them in just as a matter of habit !

What I did was place the cursor in the cell which had the text in black , and in the Immediate window , typed in :

?Activecell.Font.Color

What is displayed is the value 1315860 ; similarly , by placing the cursor in the cells which had the text in Blue and Purple , I got the values 16711680 and 16711808.

You can use these values directly in the code as follows :

Public Sub Observer()
          Dim ColorBlue As Long, ColorBlack As Long, ColorPurple As Long
          ColorBlue = 16711680   'RGB(0, 0, 255)
          ColorBlack = 1315860   'RGB(20, 20, 20)
          ColorPurple = 16711808 'RGB(128, 0, 255)
        
          ThisWorkbook.Worksheets("Sheet2").Activate
          Range("A2").Select
        
          Lastrow = Selection.End(xlDown).Row
        
          For Each cell In Range("A2").Resize(Lastrow - 1)
              With Application.WorksheetFunction
                    cellcolorindex = .Match(cell.Font.Color, Array(ColorBlue, ColorBlack, ColorPurple), 0)
                    cellcolor = .Index(Array("Blue", "Black", "Purple"), cellcolorindex)
                    ObserverName = .VLookup(cellcolor, Range("Colour_vs_Observer_Table"), 2, False)
                    cell.Offset(, 2).Value = ObserverName
              End With
          Next
End Sub

Narayan

 

[NARAYANK991]

@NARAYANK991 : Won't a text delimiter help?

Hi Abhijeet ,

I am not sure your question is related to this thread ; can you clarify ?

Narayan

 

[Abhijeet R. Joshi]

@NARAYANK991 : What I understand is that the OP wants the name of the observer in a separate column, so I thought of that approach..