VBA, which can colour A, B, C, D and E separated by vertical bar

[Kishan]

Using Excel 2000</SPAN></SPAN>

I got data in the column C (A, B, C, D and E) separated by vertical bar and there is a one space between vertical bar also that I want to coloured all different colour but each alphabet must be the same colour in the below example there are coloured only row 5 & 6 but I need to coloured as long as data are find in the column C </SPAN></SPAN>

Book1
	A	B	C	D	E
1
2
3
4			P1
5			[/COLOR][COLOR=#0000cd]B[/COLOR][COLOR=#000000] | [/COLOR][COLOR=#006400]C [/COLOR][COLOR=#000000]| [/COLOR][COLOR=#800000]D[/COLOR][COLOR=#000000] | [/COLOR][COLOR=#800000]D[/COLOR][COLOR=#000000] | [/COLOR][COLOR=#4b0082]E[/COLOR][/B][COLOR=#000000]
6			[/COLOR][COLOR=#ff0000]A[/COLOR][/B][COLOR=#000000] | [/COLOR][COLOR=#0000cd]B[/COLOR][COLOR=#000000] | [/COLOR][COLOR=#800000]D[/COLOR][COLOR=#000000] | [/COLOR][COLOR=#4b0082]E[/COLOR][COLOR=#000000] | [/COLOR][COLOR=#4b0082]E[/COLOR][COLOR=#000000]
7			B | B | B | D | D
8			A | A | D | D | D
9			B | C | C | E | E
10			D | D | D | E | E
11			A | A | A | C | E
12			A | B | C | C | D
13			A | A | A | B | C
14			B | B | C | D | D
15			A | A | C | E | E
16			A | A | B | C | C
17			B | B | C | D | D
18			A | A | C | D | D
19			B | C | D | D | E
20			A | B | D | E | E
21			A | B | D | E | E
22			A | A | A | A | E
23			A | C | C | D | E
24			A | C | C | D | D
25			A | C | C | C | D
26			A | A | B | B | E
27			C | C | D | D | E
28			A | A | C | D | D
29			A | A | B | D | E
30			A | B | C | D | E
31			B | C | E | E | E
32			A | A | A | C | D
33			A | B | C | C | E
34			A | B | B | B | D
35			A | C | D | D | D
36			B | B | C | D | E
37			C | C | C | E | E
38			B | B | C | E | E
39			A | A | B | B | E
40			B | C | C | D | E
41			A | A | C | C | D
42
43
44
45
46
Sheet16

Thank you in advance</SPAN></SPAN>

Regards</SPAN></SPAN>,</SPAN>
Kishan</SPAN></SPAN>

 

[Kishan]

If you are interested, here is my code modified for your formatted 2-digit numbers...

[TABLE="width: 500"]
<TBODY>[TR]
[TD]Sub ColorLetters()
  Dim R As Long, C As Long, Colors As Variant
  ' Numbers are the font colors for the numbers 01, 02, 03, 04 and 05 in that order
  Colors = Array(255, 13434880, 25600, 128, 8519755)
  For R = 5 To Cells(Rows.Count, "C").End(xlUp).Row
    For C = 1 To 22 Step 5
      Cells(R, "C").Characters(C, 2).Font.Color = Colors(Cells(R, "C").Characters(C, 2).Text - 1)
    Next
  Next
End Sub
[/TD]
[/TR]
</TBODY>[/TABLE]

Rick Rothstein, perfect! I appreciate your help for taking a look into this request also. </SPAN></SPAN>

Thank you for your help</SPAN></SPAN>

Kind Regards,</SPAN></SPAN>
Kishan </SPAN></SPAN>

 

[=ODIN=]

For those looking for a method of handling this with no coding knowledge, the Nutilities add-in has a nutility for this.


It can be downloaded here: http://www.iwishexcel.com/

Once installed, hit Ctrl+Alt+Shift+Enter within Excel to bring up the instructions and then checkout the Cell Multi-Font Color nutility on the Formatting/View tab.
Or you can hit Ctrl+Alt+Shift+i to get straight to the nutility.

This a pic of what the directions look lkike once oyu have it installed in excel.

https://imgur.com/AUJNXKC