Results 1 to 11 of 11
  1. #1
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post

    Conditional formatting using worksheet change event

    Hi all....I am using Excel 2003 which only permits 3 conditional formattings....I am using worksheet change event code to do a number of additional cond formattings. The code is shown here (below)......in the 6th CASE (see red font below), I am looking for some way to say that if there is text & ^ (eg: Smith^) or a number & ^ (eg: 304^) it will color the interior gray (15) with black font (1)......it won't allow me to use something like "*"&"^" or *&"^" etc etc.........the code I am using is this (except, of course the 'CASES' are not numbered as 1,2,3,4 etc -- I did that to make my explanation clearer). Any suggestions or ideas? Thanks

    Private Sub Worksheet_Change(ByVal target As Range)
    Dim lRow As Long
    Dim sClosed As String
    Dim oCell As Range

    sClosed = "Closed"
    On Error GoTo ErrHandler
    If Not Intersect(target, Range("A15:A29")) Is Nothing Then
    Range("f14:bf275").Replace What:=sClosed, Replacement:=""
    For lRow = 14 To 275
    If Cells(lRow, 4) = sClosed Then
    Range("f" & lRow & ":bf" & lRow).Value = sClosed
    End If
    Next
    End If
    If Not Intersect(target, Range("f14:bf275")) Is Nothing Then
    For Each oCell In Intersect(target, Range("f14:bf275")).Cells
    Select Case oCell
    1 Case "Conf.", "Educ. Leave"
    oCell.Interior.ColorIndex = 5
    oCell.Font.ColorIndex = 2
    2 Case "Not working"
    oCell.Interior.ColorIndex = 4
    oCell.Font.ColorIndex = 1
    3 Case "Vacation", "Vacation-AM", "Vacation-PM"
    oCell.Interior.ColorIndex = 15
    oCell.Font.ColorIndex = 1
    4 Case "Canceled"
    oCell.Interior.ColorIndex = 3
    oCell.Font.ColorIndex = 2
    5 Case "Study Week"
    oCell.Interior.ColorIndex = 13
    oCell.Font.ColorIndex = 2
    6 Case 'NUMBER/TEXT'&"^", 'NUMBER/TEXT'&"^"
    oCell.Interior.ColorIndex = 15
    oCell.Font.ColorIndex = 15
    7 Case "Closed"
    oCell.Interior.ColorIndex = 15
    oCell.Font.ColorIndex = 1
    8 Case "Duty officer"
    oCell.Interior.ColorIndex = 1
    oCell.Font.ColorIndex = 2
    9 Case Else
    oCell.Interior.ColorIndex = xlColorIndexAutomatic
    oCell.Font.ColorIndex = 1
    End Select
    Next oCell
    End If
    ExitHandler:
    Exit Sub
    ErrHandler:
    MsgBox Err.Number & Err.Description
    Resume ExitHandler
    End Sub

  2. #2
    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
    If I understand what you want, you can eliminate your "6 case" and add to your "9 Case Else" t look for a string ending in a caret (^):
    Code:
      Case Else
        If Right(oCell, 1) = "^" Then
          oCell.Interior.ColorIndex = 15
          oCell.Font.ColorIndex = 15
        Else
          oCell.Interior.ColorIndex = xlColorIndexAutomatic
          oCell.Font.ColorIndex = 1
        End If
    End Select

  3. #3
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post
    Exactly....and I was also able to modify it to use in a couple of other w/sheets in same w/book...thanks, Steve

  4. #4
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post
    Hi Steve....I am now trying to also color the catet (^) the same color as the interior...at the moment, I am actually using "oCell.interior.ColorIndex=45 and oCell.Font.ColorIndex=1" and have added the line "oCell.Characters("^:^").ColorIndex = 45 (trying to color the caret the same as the interior)....I also tried "oCell.Characters(oCell, "^").Font.ColorIndex = 45" but in both instances, I get an error message that says "1004 Unable to get the characters property of the Range class"........what am I doing wrong?

  5. #5
    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
    Something like (presuming the last character is the caret):
    Code:
    If Right(oCell, 1) = "^" Then
      oCell.Characters(Start:=Len(oCell), Length:=1).Font.ColorIndex = 15
    End If
    Steve

  6. #6
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post
    This works exactly....thank you,again

  7. #7
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post
    Hi Steve....I am trying to use W/sheet change events (Activate) to do some cond formatting....I have a range b10:x500....every second row has been colored light blue (color #34) to make the rows easier to read. The default data in each cell is like this: 07-Jan-11 @ 9:00--#100

    There is formula in each cell that can change the cell to display either "Closed", or, "-----". This happens based on entries made on a separate worksheet. My w/sheet change event coloring is the following:

    Private Sub Worksheet_Activate()
    Dim oCell As Range
    For Each oCell In Range("b10:x500").Cells
    Select Case oCell
    Case "Closed"
    oCell.Interior.ColorIndex = 15
    oCell.Font.ColorIndex = 1
    Case "-------"
    oCell.Interior.ColorIndex = 15
    oCell.Font.ColorIndex = 1
    End Select
    Next oCell
    End Sub

    As I mentioned above, the "Closed" and the "-------" result from making entries on another worksheet. When these occur, the cell is colored gray. If I undo those entries on that other worksheet, the "Closed" or the "-------" will also be undone, but the gray coloring (#15) remains in the cell.....how can I get the cell to 'un-gray' and go back to its original interior cell color, especially if rows alternate in color between 'no color' (automatic?) and the light blue (color #37).....??

  8. #8
    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
    The worksheet activate occurs when the worksheet is activated (change from a different worksheet to this worksheet) it is not the same as a the worksheet change. If you want to change the format when a cell is changed you need a worksheet change event.

    Your case structure only looks at the case when "Closed" and the "-------" If you want to change it when it is NOT one of those you can use a "Case Else" type of structure and have an IF to decide (based on whatever criteria you are using) for whether it is "no color" or "light blue"

    Steve

  9. #9
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post
    Hi Steve...thank you for this....I figured out a different way to do this, but appreciate your info and advice...

  10. #10
    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
    Care to share it so others can see an alternate approach...

    Steve

  11. #11
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post
    Sure, altho I doubt that it is of much use, b/c my decision was to remove the color coding that reads:
    Case "-------"
    oCell.Interior.ColorIndex = 15
    oCell.Font.ColorIndex = 1

    .....this meant that I didn't need to worry any longer about how to get the alternately colored rows (white, light blue) to 'return' to their original colors...and the gray "Closed" cells will do so once the event that results in "Closed" is changed.....nothing very innovative, I am afraid....however, I can tell you that I am almost finished making the 'scheduling' file that I have been working on (in another thread) and I will be happy to load that once done, as it may be useful for people who wish to use Excel to perform this function -- I know that using Excel may not be a 1st choice, but on occasion, it may be the only choice...so I am going to post the file (altho I will have to reduce it's size b/c it is about 6MB, so I will have to pare it down a bit in order to be able to upload it).

Posting Permissions

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