Conditional formatting using worksheet change event

Topic

New Reply

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

Reply | Quote

Replies

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

Reply | Quote

Exactly….and I was also able to modify it to use in a couple of other w/sheets in same w/book…thanks, Steve

Reply | Quote

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?

Reply | Quote

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

Reply | Quote

This works exactly….thank you,again

Reply | Quote

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)…..??

Reply | Quote

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

Reply | Quote

Hi Steve…thank you for this….I figured out a different way to do this, but appreciate your info and advice…

Reply | Quote

Care to share it so others can see an alternate approach…

Steve

Reply | Quote

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).

Reply | Quote