VBA - split cell value by font colour

Question

I've been given a spreadsheet with a range of cells, each of which contains a list of numbers.

Each number has been given a font colour, so in one cell you might have two orange numbers, a red one and a green one. I need to treat each colour differently; for example, I can count green and red numbers as they are, but I need to see if each orange number occurs in previous cells in the range before counting it.

The number lists are comma-separated, so getting individual numbers shouldn't be a problem, but how do you retain and work with the colour information?

I'd post code, but frankly I'm not sure where to start.

Thanks in advance!

Answer 1

Solved using this:

http://www.mrexcel.com/forum/excel-questions/656265-excel-visual-basic-applications-count-items-cell-color-font.html

Essentially a loop that uses InStr to find commas in a string (in this case, the contents of a cell), then looks at the colour of the next character after the comma. I've just edited it to say if the ColorIndex = 46, copy the subsequent characters into a space in an array. Then I can look at each item in the array, and compare it with other cells in the range to see if it appears elsewhere.