• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Shade every other in column

2

2011gerry007

Member
Does anyone now how to background shade every other alpha character in a column, I have sorted rows of data on one column which contain just single variable amounts of alpha chars A<>Z
I found this code and thought I might tweek it, but I find its a little to Cumbersometo adapt to suit my situation, so I thought there has to be a better way using less code, example ...so if the first letters in the column are "A" then shade (grey) and then skip the next set of letters which might be B or possibly C, and shade D skip E shade F etc etc
any help appreciated.

Code: 
Sub Update_Row_Colors()

  Dim LRow As Integer
  Dim LCell As String
  Dim LColorCells As String

  'Start at row 7
  LRow = 4

  'Update row colors for the first 2000 rows
  While LRow < 500
      LCell = "G" & LRow
      'Color will changed in columns A to K
      LColorCells = "G" & LRow '& ":" & "G" & LRow

      Select Case Left(Range(LCell).Value, 6)

        'Set row color to light blue
        Case "a"
            Range(LColorCells).Interior.ColorIndex = 34
            Range(LColorCells).Interior.Pattern = xlSolid

        'Set row color to light green
        Case "b"
            Rows(LRow & ":" & LRow).Select
            Range(LColorCells).Interior.ColorIndex = 35
            Range(LColorCells).Interior.Pattern = xlSolid

        'Set row color to light yellow
        Case "c"
            Rows(LRow & ":" & LRow).Select
            Range(LColorCells).Interior.ColorIndex = 34
            Range(LColorCells).Interior.Pattern = xlSolid
           
           
        'Set row color to light blue
        Case "d"
            Range(LColorCells).Interior.ColorIndex = 35
            Range(LColorCells).Interior.Pattern = xlSolid

        'Set row color to light green
        Case "e"
            Rows(LRow & ":" & LRow).Select
            Range(LColorCells).Interior.ColorIndex = 34
            Range(LColorCells).Interior.Pattern = xlSolid

        'Set row color to light yellow
        Case "f"
            Rows(LRow & ":" & LRow).Select
            Range(LColorCells).Interior.ColorIndex = 35
            Range(LColorCells).Interior.Pattern = xlSolid
           
           
                  'Set row color to light blue
        Case "g"
            Range(LColorCells).Interior.ColorIndex = 34
            Range(LColorCells).Interior.Pattern = xlSolid

        'Set row color to light green
        Case "h"
            Rows(LRow & ":" & LRow).Select
            Range(LColorCells).Interior.ColorIndex = 35
            Range(LColorCells).Interior.Pattern = xlSolid

        'Set row color to light yellow
        Case "i"
            Rows(LRow & ":" & LRow).Select
            Range(LColorCells).Interior.ColorIndex = 34
            Range(LColorCells).Interior.Pattern = xlSolid
        'Default all other rows to no color
       
        Case Else
            Rows(LRow & ":" & LRow).Select
            Range(LColorCells).Interior.ColorIndex = xlNone

      End Select

      LRow = LRow + 1
  Wend

  Range("A1").Select

End Sub
 
You must log in or register to reply here.
Back
Top