Results 1 to 4 of 4
  1. #1
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Conditional Formatting VB (2000 SR-1)

    <img src=/S/whisper.gif border=0 alt=whisper width=29 height=17>You forgot to attach the file.... make sure you attach it after you preview it

    Steve

  2. #2
    Star Lounger
    Join Date
    Jun 2006
    Posts
    88
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Conditional Formatting VB (2000 SR-1)

    I searched the forum and found some usefull information on this. However, I want to keep code simple and I don't know if VB can do this.

    Since I'm limited to 3 conditional formats, I would like to figure a way to have VB do this.

    Cells Containing User entered info are columns A:I

    Column B contains a user entered date. The current VB code updates based on a change event in cell F, G or H. When this happens, I also want VB to check If today() is >= the date in cell B#, then I want cells A#:I# to be highlighted. The highlighted color will be based on the month() of cell B#. Each month will have a different color code assigned. My initial thoughts are to have a chart with 1 - 12 (for Jan - Feb) and the color code I want associated with each and somehow reference this chart (like a vlookup), but I don't know if VB can do that. Also, to note, if today() is < the date in cell B#, I don't want any formatting to take place as some future dates may be typed for known upcoming transactions, but I don't want them highlighted yet.

    Any help would be appreciated. Attached is the sample Legare was helping me with yesterday with the inital change event. I would like to implement all of this into the same worksheet. There is two tabs one is a sample of what I am trying to get.

    Thanks!! <img src=/S/cooked.gif border=0 alt=cooked width=50 height=46>

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Conditional Formatting VB (2000 SR-1)

    Try this:

    Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("B:B,F:H")) Is Nothing Then
    Application.EnableEvents = False
    Call MySort
    Call ColorMe
    Application.EnableEvents = True
    End If
    End Sub

    Private Sub MySort()
    Range("A:H").Sort _
    Key1:=Range("B2"), Key2:=Range("C2"), Key3:=Range("E2"), Header:=xlYes
    End Sub

    Private Sub ColorMe()
    Dim i As Long
    For i = 2 To Range("B65536").End(xlUp).Row
    If Range("B" & i) <= Date Then
    Range("A" & i & ":I" & i).Interior.ColorIndex = Month(Range("B" & i))
    Else
    Range("A" & i & ":I" & i).Interior.ColorIndex = xlColorIndexNone
    End If
    Next i
    End Sub

  4. #4
    Star Lounger
    Join Date
    Jun 2006
    Posts
    88
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Conditional Formatting VB (2000 SR-1)

    Brilliant! Couldn't have done it better myself. Oh, wait, couldn't have done it at all... <img src=/S/bravo.gif border=0 alt=bravo width=16 height=30>

    Code worked perfectly. I went in and changed the color palette to get the colors I wanted for the corresponding month number.

    Mucho Gusto!!

Posting Permissions

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