Fill colours as per numbers of characters

[Kishan]

Using Excel 2000</SPAN></SPAN>
Hi,</SPAN></SPAN>

Columns L:N, I got numbers fills for the character 1, X & 2, my query is to fill colours in the columns C:I as per numbers of character, for example... </SPAN></SPAN>
M6="X"=1 fill C6=1"X" with green fill and white font.</SPAN></SPAN>
M7="X"=2 fill C7 & D7=2"X" with green fill and white font, N7="2"=1 fill E7=1"2" with blue fill and white font... and so on.... </SPAN></SPAN>

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1
2
3
4
5			P1	P2	P3	P4	P5	P6	P7			1	X	2
6			X	1	1	1	2	1	2				1
7			X	X	2	1	X	X	1				2	1
8			X	1	1	1	2	1	2				1
9			2	1	1	X	2	1	X					1
10			1	X	1	X	1	1	2			1	1
11			X	2	X	X	2	X	1				1	1
12			X	1	1	2	1	1	1				1
13			1	X	X	2	1	X	1			1	2	1
14			1	1	1	2	X	X	X			3		1
15			1	X	X	1	1	1	1			1	2
16			X	1	X	2	1	2	X				1
17			1	1	X	1	2	1	X			2	1
18			1	1	1	1	1	X	1			5	1
19			X	1	1	X	X	1	1				1
20			1	1	1	1	2	1	X			4		1
21			1	1	1	2	2	1	1			3		2
22			2	1	X	1	1	X	1					1
23			1	1	1	1	1	2	1			5		1
24			X	1	1	2	1	1	2				1
25			1	1	1	1	X	1	1			4	1
26
27
Sheet3

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

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

 

[Fluff]

How about

Sub Kishan()
   Dim Cl As Range
   
   For Each Cl In Range("L6:L" & Range("C" & Rows.Count).End(xlUp).Row)
      If Cl <> "" Then
         With Cl.Offset(, -9).Resize(, Cl)
            .Interior.Color = Range("L5").Interior.Color
            .Font.Color = vbWhite
         End With
      End If
      If Cl.Offset(, 1) <> "" Then
         With Cl.Offset(, -9 + Cl).Resize(, Cl.Offset(, 1))
            .Interior.Color = Range("M5").Interior.Color
            .Font.Color = vbWhite
         End With
      End If
      If Cl.Offset(, 2) <> "" Then
         With Cl.Offset(, -9 + Cl + Cl.Offset(, 1)).Resize(, Cl.Offset(, 2))
            .Interior.Color = Range("N5").Interior.Color
            .Font.Color = vbWhite
         End With
      End If
   Next Cl
End Sub

 

[Kishan]

How about

Sub Kishan()
   Dim Cl As Range
   
   For Each Cl In Range("L6:L" & Range("C" & Rows.Count).End(xlUp).Row)
      If Cl <> "" Then
         With Cl.Offset(, -9).Resize(, Cl)
            .Interior.Color = Range("L5").Interior.Color
            .Font.Color = vbWhite
         End With
      End If
      If Cl.Offset(, 1) <> "" Then
         With Cl.Offset(, -9 + Cl).Resize(, Cl.Offset(, 1))
            .Interior.Color = Range("M5").Interior.Color
            .Font.Color = vbWhite
         End With
      End If
      If Cl.Offset(, 2) <> "" Then
         With Cl.Offset(, -9 + Cl + Cl.Offset(, 1)).Resize(, Cl.Offset(, 2))
            .Interior.Color = Range("N5").Interior.Color
            .Font.Color = vbWhite
         End With
      End If
   Next Cl
End Sub

Hi Fluff, macro worked excellent as request. Thank you so much for your help and time.</SPAN></SPAN>

Have a nice weekend</SPAN></SPAN>

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

 

[Fluff]

You're welcome & thanks for the feedback.

Here's a slighly simpler version if your interested.

Sub Kishan()
   Dim Cl As Range
   
   For Each Cl In Range("L6:L" & Range("C" & Rows.Count).End(xlUp).Row)
      If Cl <> "" Then
        Cl.Offset(, -9).Resize(, Cl).Interior.Color = Range("L5").Interior.Color
      End If
      If Cl.Offset(, 1) <> "" Then
         Cl.Offset(, -9 + Cl).Resize(, Cl.Offset(, 1)).Interior.Color = Range("M5").Interior.Color
      End If
      If Cl.Offset(, 2) <> "" Then
         Cl.Offset(, -9 + Cl + Cl.Offset(, 1)).Resize(, Cl.Offset(, 2)).Interior.Color = Range("N5").Interior.Color
      End If
      Cl.Offset(, -9).Resize(, Application.Sum(Cl.Resize(, 3))).Font.Color = vbWhite
   Next Cl
End Sub

 

[Kishan]

You're welcome & thanks for the feedback.

Here's a slighly simpler version if your interested.

Sub Kishan()
   Dim Cl As Range
   
   For Each Cl In Range("L6:L" & Range("C" & Rows.Count).End(xlUp).Row)
      If Cl <> "" Then
        Cl.Offset(, -9).Resize(, Cl).Interior.Color = Range("L5").Interior.Color
      End If
      If Cl.Offset(, 1) <> "" Then
         Cl.Offset(, -9 + Cl).Resize(, Cl.Offset(, 1)).Interior.Color = Range("M5").Interior.Color
      End If
      If Cl.Offset(, 2) <> "" Then
         Cl.Offset(, -9 + Cl + Cl.Offset(, 1)).Resize(, Cl.Offset(, 2)).Interior.Color = Range("N5").Interior.Color
      End If
      Cl.Offset(, -9).Resize(, Application.Sum(Cl.Resize(, 3))).Font.Color = vbWhite
   Next Cl
End Sub

Hi Fluff, I appreciate your help. It worked fine. </SPAN></SPAN>

Good Luck</SPAN></SPAN>

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

 

[Fluff]

My pleasure