Fill colours as per numbers of characters

Kishan

Well-known Member
Joined
Mar 15, 2011
Messages
1,645
Office Version
  1. 2010
Platform
  1. Windows
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
ABCDEFGHIJKLMNOP
1
2
3
4
5P1P2P3P4P5P6P71X2
6X1112121
7XX21XX121
8X1112121
9211X21X1
101X1X11211
11X2XX2X111
12X1121111
131XX21X1121
141112XXX31
151XX111112
16X1X212X1
1711X121X21
1811111X151
19X11XX111
20111121X41
21111221132
2221X11X11
23111112151
24X1121121
251111X1141
26
27
Sheet3


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

Regards,</SPAN></SPAN>
Kishan</SPAN></SPAN>
 
Last edited:

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
How about
Code:
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
 
Upvote 0
How about
Code:
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 :biggrin:
</SPAN></SPAN>
 
Upvote 0
You're welcome & thanks for the feedback.

Here's a slighly simpler version if your interested.
Code:
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
 
Upvote 0
You're welcome & thanks for the feedback.

Here's a slighly simpler version if your interested.
Code:
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>
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,034
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top