Results 1 to 6 of 6

Thread: Fill colours as per numbers of characters

  1. #1
    Board Regular
    Join Date
    Mar 2011
    Posts
    1,495
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Fill colours as per numbers of characters

    Using Excel 2000
    Hi,

    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...
    M6="X"=1 fill C6=1"X" with green fill and white font.
    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....

    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

    Regards,
    Kishan
    Last edited by Kishan; Jul 13th, 2019 at 10:45 AM.

  2. #2
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,190
    Post Thanks / Like
    Mentioned
    470 Post(s)
    Tagged
    47 Thread(s)

    Default Re: Fill colours as per numbers of characters

    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
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  3. #3
    Board Regular
    Join Date
    Mar 2011
    Posts
    1,495
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Fill colours as per numbers of characters

    Quote Originally Posted by Fluff View Post
    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.

    Have a nice weekend


    Kind Regards,

    Kishan


  4. #4
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,190
    Post Thanks / Like
    Mentioned
    470 Post(s)
    Tagged
    47 Thread(s)

    Default Re: Fill colours as per numbers of characters

    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
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  5. #5
    Board Regular
    Join Date
    Mar 2011
    Posts
    1,495
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Fill colours as per numbers of characters

    Quote Originally Posted by Fluff View Post
    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.

    Good Luck


    Kind Regards,

    Kishan

  6. #6
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,190
    Post Thanks / Like
    Mentioned
    470 Post(s)
    Tagged
    47 Thread(s)

    Default Re: Fill colours as per numbers of characters

    My pleasure
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •