Changing colors of cards based on suits over multiple columns

[Treys]

This was solved in a previous thread "s there a way to replace specific letters in a range of cells with symbols?".
However, i'm having some trouble getting the ranges correct. I would like to have three columns, one for each of the three cards. I've been playing around with the ranges in VBA but nothing seems to work.
Heres the vba code that gets it correct for one column:

This code resulted in this:

I would like to split the cards like this:

As you can see, i only get colours on the first column. Would love to get some feedback on this, and if possible a little explanation so that i can understand this. Pretty frustrating when nothing seems to work haha.
Cheers!

 

[Fluff]

Hi & welcome to MrExcel.

Can you post some sample data of what you start with.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[Treys]

Suits done.xlsm
	A	B
1	A♠ 3♠ 2♦
2	8♠ 4♠ 2♦
3	A♠ 4♠ 2♦
4	6♠ 5♠ 2♦
5	J♠ 5♠ 2♦
6	T♠ 7♠ 2♦
7	K♠ 8♠ 2♦
8	Q♠ 8♠ 2♦
9	A♠ T♠ 2♦
10	Q♠ T♠ 2♦
11	6♣ 2♠ 2♦
12	T♣ 2♠ 2♦
13	5♣ 3♦ 2♠
14	6♣ 3♦ 2♠
15	7♣ 3♦ 2♠
16	7♠ 3♦ 2♠
17	K♠ 3♦ 2♠
18	Q♣ 3♦ 2♠
19	4♠ 3♠ 2♠
20	9♦ 3♠ 2♠
21	5♣ 4♦ 2♠
22	9♣ 4♦ 2♠
23	7♦ 4♠ 2♠
24	6♠ 5♦ 2♠
25	J♠ 5♦ 2♠
26	T♣ 5♦ 2♠
27	J♣ 6♦ 2♠
28	8♦ 6♠ 2♠
29	Q♦ 6♠ 2♠
30	9♠ 7♦ 2♠
31	A♠ 7♦ 2♠
32	T♣ 7♦ 2♠
33	A♣ 8♦ 2♠
34	K♣ 8♦ 2♠
35	T♣ 8♦ 2♠
36	T♠ 8♠ 2♠
37	Q♠ J♦ 2♠
38	K♦ Q♠ 2♠
39	Q♣ T♦ 2♠
40	5♠ 4♠ 3♦
41	8♠ 5♠ 3♦
42	A♠ 6♠ 3♦
43	J♠ 8♠ 3♦
44	A♠ J♠ 3♦
45	K♠ Q♠ 3♦
46	8♠ 3♠ 3♦
47	J♣ 3♠ 3♦
48	A♣ 4♦ 3♠
49	J♣ 4♦ 3♠
50	K♠ 4♦ 3♠
51	6♠ 4♠ 3♠
52	A♣ 5♦ 3♠
53	J♦ 5♠ 3♠
54	K♣ 6♦ 3♠
55	9♦ 6♠ 3♠
56	8♣ 7♦ 3♠
57	8♠ 7♦ 3♠
58	A♣ 7♦ 3♠
59	Q♣ 7♦ 3♠
60	A♦ 7♠ 3♠
61	J♠ 7♠ 3♠
62	9♣ 8♦ 3♠
63	T♣ 9♦ 3♠
64	Q♠ 9♠ 3♠
65	A♠ Q♠ 3♠
66	A♣ T♦ 3♠
67	K♦ T♠ 3♠
68	A♠ 5♠ 4♦
69	T♠ 5♠ 4♦
70	8♠ 6♠ 4♦
71	Q♠ 7♠ 4♦
72	9♠ 8♠ 4♦
73	J♠ 9♠ 4♦
74	4♠ 4♦ 3♣
75	K♣ 4♠ 4♦
76	K♣ 5♦ 4♠
77	K♠ 5♦ 4♠
78	K♦ 5♠ 4♠
79	7♠ 6♦ 4♠
80	9♣ 6♦ 4♠
81	A♣ 6♦ 4♠
82	K♣ 6♦ 4♠
83	Q♣ 6♦ 4♠
84	J♠ 6♠ 4♠
85	8♣ 7♦ 4♠
86	T♦ 7♠ 4♠
87	J♣ 8♦ 4♠
88	Q♣ 8♦ 4♠
89	Q♣ 9♦ 4♠
90	J♠ 9♠ 4♠
91	Q♣ J♦ 4♠
92	J♣ T♦ 4♠
93	A♦ T♠ 4♠
94	7♠ 6♠ 5♦
95	8♠ 6♠ 5♦
96	8♠ 7♠ 5♦
97	9♠ 7♠ 5♦
98	Q♠ J♠ 5♦
99	7♣ 5♠ 5♦
100	8♠ 6♦ 5♠
101	A♣ 6♦ 5♠
102	J♣ 6♦ 5♠
103	K♠ 6♠ 5♠
104	9♠ 7♦ 5♠
105	A♠ 7♠ 5♠
106	Q♦ 7♠ 5♠
107	9♣ 8♦ 5♠
108	A♠ 8♠ 5♠
109	K♣ 9♦ 5♠
110	Q♠ 9♦ 5♠
111	Q♦ 9♠ 5♠
112	T♦ 9♠ 5♠
113	A♦ J♠ 5♠
114	A♣ T♦ 5♠
115	K♠ T♦ 5♠
116	Q♣ T♦ 5♠
117	Q♦ T♠ 5♠
118	A♠ T♠ 6♦
119	8♠ 6♠ 6♦
120	9♣ 6♠ 6♦
121	9♠ 6♠ 6♦
122	J♠ 6♠ 6♦
123	A♣ 7♦ 6♠
124	T♣ 7♦ 6♠
125	K♦ 7♠ 6♠
126	9♣ 8♦ 6♠
127	Q♠ 8♦ 6♠
128	T♠ 8♦ 6♠
129	T♠ 9♦ 6♠
130	A♠ 9♠ 6♠
131	K♠ J♦ 6♠
132	A♣ Q♦ 6♠
133	K♠ Q♠ 6♠
134	J♣ T♦ 6♠
135	A♠ K♠ 7♦
136	K♠ Q♠ 7♦
137	Q♣ 7♠ 7♦
138	T♠ 7♠ 7♦
139	J♣ 8♦ 7♠
140	T♦ 8♠ 7♠
141	J♣ 9♦ 7♠
142	K♠ 9♠ 7♠
143	A♠ Q♦ 7♠
144	A♦ Q♠ 7♠
145	K♦ Q♠ 7♠
146	A♣ T♦ 7♠
147	A♠ K♠ 8♦
148	8♠ 8♦ 3♠
149	T♣ 8♠ 8♦
150	T♠ 9♦ 8♠
151	A♦ 9♠ 8♠
152	K♣ J♦ 8♠
153	A♦ K♠ 8♠
154	J♠ T♦ 8♠
155	J♠ T♠ 9♦
156	9♠ 9♦ 3♣
157	9♠ 9♦ 3♠
158	A♣ 9♠ 9♦
159	Q♣ J♦ 9♠
160	A♠ K♦ 9♠
161	A♦ Q♠ 9♠
162	K♦ Q♠ 9♠
163	A♦ T♠ 9♠
164	A♠ A♦ 5♣
165	A♠ A♦ A♣
166	A♠ A♦ J♠
167	J♣ 8♦ 4♠
168	A♠ Q♠ J♦
169	J♠ J♦ 4♣
170	J♠ J♦ 5♣
171	J♠ J♦ 7♣
172	A♦ K♠ J♠
173	K♣ Q♦ J♠
174	K♠ K♦ 4♠
175	K♠ K♦ 6♣
176	K♠ K♦ 9♣
177	A♠ K♠ K♦
178	K♠ K♦ T♣
179	A♠ K♦ Q♠
180	Q♠ Q♦ 3♣
181	Q♠ Q♦ 8♠
182	Q♠ J♠ T♦
183	K♠ J♠ T♠
184	T♠ T♦ 5♣
185	T♠ T♦ T♣
Sheet1

 

[Treys]

Suits done.xlsm
	A	B	C
67	K♦	T♠	3♠
68	A♠	5♠	4♦
69	T♠	5♠	4♦
70	8♠	6♠	4♦
71	Q♠	7♠	4♦
72	9♠	8♠	4♦
73	J♠	9♠	4♦
74	4♠	4♦	3♣
75	K♣	4♠	4♦
76	K♣	5♦	4♠
77	K♠	5♦	4♠
78	K♦	5♠	4♠
79	7♠	6♦	4♠
80	9♣	6♦	4♠
81	A♣	6♦	4♠
82	K♣	6♦	4♠
83	Q♣	6♦	4♠
84	J♠	6♠	4♠
85	8♣	7♦	4♠
86	T♦	7♠	4♠
87	J♣	8♦	4♠
88	Q♣	8♦	4♠
89	Q♣	9♦	4♠
90	J♠	9♠	4♠
91	Q♣	J♦	4♠
92	J♣	T♦	4♠
93	A♦	T♠	4♠
94	7♠	6♠	5♦
95	8♠	6♠	5♦
96	8♠	7♠	5♦
97	9♠	7♠	5♦
98	Q♠	J♠	5♦
99	7♣	5♠	5♦
100	8♠	6♦	5♠
101	A♣	6♦	5♠
102	J♣	6♦	5♠
103	K♠	6♠	5♠
104	9♠	7♦	5♠
105	A♠	7♠	5♠
106	Q♦	7♠	5♠
107	9♣	8♦	5♠
108	A♠	8♠	5♠
109	K♣	9♦	5♠
110	Q♠	9♦	5♠
111	Q♦	9♠	5♠
112	T♦	9♠	5♠
113	A♦	J♠	5♠
114	A♣	T♦	5♠
115	K♠	T♦	5♠
116	Q♣	T♦	5♠
117	Q♦	T♠	5♠
118	A♠	T♠	6♦
119	8♠	6♠	6♦
120	9♣	6♠	6♦
121	9♠	6♠	6♦
122	J♠	6♠	6♦
123	A♣	7♦	6♠
124	T♣	7♦	6♠
125	K♦	7♠	6♠
126	9♣	8♦	6♠
127	Q♠	8♦	6♠
128	T♠	8♦	6♠
129	T♠	9♦	6♠
130	A♠	9♠	6♠
131	K♠	J♦	6♠
132	A♣	Q♦	6♠
133	K♠	Q♠	6♠
134	J♣	T♦	6♠
135	A♠	K♠	7♦
136	K♠	Q♠	7♦
137	Q♣	7♠	7♦
138	T♠	7♠	7♦
139	J♣	8♦	7♠
140	T♦	8♠	7♠
141	J♣	9♦	7♠
142	K♠	9♠	7♠
143	A♠	Q♦	7♠
144	A♦	Q♠	7♠
145	K♦	Q♠	7♠
146	A♣	T♦	7♠
147	A♠	K♠	8♦
148	8♠	8♦	3♠
149	T♣	8♠	8♦
150	T♠	9♦	8♠
151	A♦	9♠	8♠
152	K♣	J♦	8♠
153	A♦	K♠	8♠
154	J♠	T♦	8♠
155	J♠	T♠	9♦
156	9♠	9♦	3♣
157	9♠	9♦	3♠
158	A♣	9♠	9♦
159	Q♣	J♦	9♠
160	A♠	K♦	9♠
161	A♦	Q♠	9♠
162	K♦	Q♠	9♠
163	A♦	T♠	9♠
164	A♠	A♦	5♣
165	A♠	A♦	A♣
166	A♠	A♦	J♠
167	J♣	8♦	4♠
168	A♠	Q♠	J♦
169	J♠	J♦	4♣
170	J♠	J♦	5♣
171	J♠	J♦	7♣
172	A♦	K♠	J♠
173	K♣	Q♦	J♠
174	K♠	K♦	4♠
175	K♠	K♦	6♣
176	K♠	K♦	9♣
177	A♠	K♠	K♦
178	K♠	K♦	T♣
179	A♠	K♦	Q♠
180	Q♠	Q♦	3♣
181	Q♠	Q♦	8♠
182	Q♠	J♠	T♦
183	K♠	J♠	T♠
184	T♠	T♦	5♣
185	T♠	T♦	T♣
Sheet4

 

[Treys]

Hope these are better! Let me know if i can post anything else that may be helpful

 

[Fluff]

Thanks for that.
With the 2nd example

Sub Treys()
   Dim Cl As Range
   
   For Each Cl In Range("A1").CurrentRegion
      Select Case AscW(Right(Cl.Value, 1))
         Case 9829: Cl.Characters(2, 1).Font.ColorIndex = 3
         Case 9830: Cl.Characters(2, 1).Font.ColorIndex = 23
         Case 9827: Cl.Characters(2, 1).Font.ColorIndex = 50
         Case 9824: Cl.Characters(2, 1).Font.ColorIndex = 1
      End Select
   Next Cl
End Sub

 

[Treys]

Thank you!

 

[Fluff]

You're welcome.