Results 1 to 7 of 7

Color code cell with case statement and datestamp cell(s) to the left

Threaded View

  1. #1
    Registered User
    Join Date
    04-06-2010
    Location
    San Diego, CA
    MS-Off Ver
    Excel 2003
    Posts
    16

    Color code cell with case statement and datestamp cell(s) to the left

    Hello,

    I've been wrestling with this code for quite some time which I got off of a forum that I'm trying to edit it without much luck. I have been searching this forum and others to find a solution. Currently, I'm using this code to color the cell based on a data validation list that works perfectly. I use this code so that I can copy/paste and autofill the values down, but I need a static datestamp that is inputted in the column to the left for each cell OR delete the date stamp if the cell is empty.

    I've tried to add a datestamp for each case and I must not understand VBA enough because it keeps crashing my Excel when I change the data validation list. I really believe there is something very simple that I'm not getting.

    Any help would be greatly appreciated

    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim lastrow As Long, i As Long
    lastrow = Cells(Rows.Count, 23).End(xlUp).Row
    Application.ScreenUpdating = False
    For i = 11 To 500
        If Cells(i, 23).Value <> "" Then Cells(i, 23).FormatConditions.Delete
        Select Case (Cells(i, 23).Value)
        Case "Case1", "Case2":
            Cells(i, 23).Interior.ColorIndex = 37
        Case "Case3", "Case4":
            Cells(i, 23).Interior.ColorIndex = 3
    '''More cases here'''
        Case Else
            Cells(i, 23).Interior.ColorIndex = xlNone
        End Select
    Next i
    Application.ScreenUpdating = True
    End Sub
    Last edited by garricko; 09-26-2010 at 08:40 PM.

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