+ Reply to Thread
Results 1 to 3 of 3

vba To Update A Column

Hybrid View

  1. #1
    Valued Forum Contributor
    Join Date
    11-15-2007
    Location
    England
    MS-Off Ver
    Office Pro 2003 / 2010
    Posts
    423

    vba To Update A Column

    This macro identifies specifc codes in column A and changes the colour of the cell accordingingly.

    Can someone give me a macro that will run down column A starting at A6 and edit the cell and move on down to A160 so that all of the formats are correct?

    Many thanks


    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim c As Range, d As Range, bc As Long
    Set d = Intersect(Range("A6:A160"), Target)
    If d Is Nothing Then Exit Sub
    For Each c In d
        Select Case UCase(c)
            Case "YNPH"
                bc = 22
            Case "WFBC"
                bc = 40
            Case "WFT"
                bc = 6
            Case "WFA"
                bc = 37
            Case "WF"
                bc = 35
            Case "AFCC" '
                bc = 28
            Case "WFBQ"
                bc = 19
            Case "WFB"
                bc = 26
            Case "WFJ"
                bc = 17
            Case "WFTM"
                bc = 24
            Case "AFF" '
                bc = 22
            Case "AFC" '
                bc = 50
            Case "AFCL"
                bc = 47
            Case "WFU"
                bc = 46
            Case "WFBS"
                bc = 45
            Case "AFKG"
                bc = 54
            Case "WBVS"
                bc = 43
            Case "WFL"
                bc = 42
            Case Else
                fc = 1: fb = False: bc = xlNone
        End Select
        With Cells(c.Row, 1).Resize(, 13)
        .Interior.ColorIndex = bc
        End With
    Next
    End Sub

  2. #2
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: vba To Update A Column

    Have you considered using conditional formatting rather than VBA? It would be more efficient.
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  3. #3
    Valued Forum Contributor
    Join Date
    11-15-2007
    Location
    England
    MS-Off Ver
    Office Pro 2003 / 2010
    Posts
    423

    Re: vba To Update A Column

    I'm using Office 2003 at work and that only allows three options for conditional formatting, or is there a cheat that I don't know about?

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1