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

    Fine-tuning some color coding (Excel 2003)

    Good morning, loungers...I need some help fine-tuning some color coding....I am using this code:

    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim oCell As Range
    If Not Intersect(Target, Range("d22:s282")) Is Nothing Then
    For Each oCell In Intersect(Target, Range("d22:s282")).Cells
    Select Case oCell
    Case "Away", "away", "Not sitting", "N/S", "n/s"
    oCell.Interior.ColorIndex = 4
    oCell.Font.ColorIndex = 1
    Case "Xvac", "xvac", "Xcon", "xcon"
    oCell.Interior.ColorIndex = 1
    oCell.Font.ColorIndex = 2
    Case "VAC", "vac", "Vac"
    oCell.Interior.ColorIndex = 15
    oCell.Font.ColorIndex = 1
    Case "CON", "con", "Con"
    oCell.Interior.ColorIndex = 5

    ..to trigger some format changes when data is entered......if I use CON ( denotes a Conference) I would like to be able, for example, to add something after CON (eg: How to be healthy) after CON....I tried to use a wildcard ( "CON"&*....or "Con"&"-"&* and it won't work.....is there a way to permit the entry of other data following CON, Con etc that will still allow the formatting change?

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

    Re: Fine-tuning some color coding (Excel 2003)

    You can't use wildcards in a Case statement. You could do something like this instead:

    If UCase(oCell) Like "CON*" Then
    oCell.Interior.ColorIndex = 5
    Else
    Select Case oCell
    ...
    End Select
    End If

  3. #3
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Fine-tuning some color coding (Excel 2003)

    Hi Hans.....I have set it up like this, but it 'stops' and hilights "End Sub".............and is there a way to enter more 'choices' that just "CON*" (although I recall that UCase allow upper or lower case when typing Con) ?

    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim oCell As Range
    If Not Intersect(Target, Range("d22:s282")) Is Nothing Then
    For Each oCell In Intersect(Target, Range("d22:s282")).Cells
    If UCase(oCell) Like "CON*" Then
    oCell.Interior.ColorIndex = 5
    oCell.Font.ColorIndex = 2
    Else
    Select Case oCell
    Case "Away", "away", "Not sitting", "N/S", "n/s"
    oCell.Interior.ColorIndex = 4
    oCell.Font.ColorIndex = 1
    Case "Xvac", "xvac", "Xcon", "xcon"
    oCell.Interior.ColorIndex = 1
    oCell.Font.ColorIndex = 2
    Case "VAC", "vac", "Vac"
    oCell.Interior.ColorIndex = 15
    oCell.Font.ColorIndex = 1
    Case "CJ", "cj", "Cj"
    oCell.Interior.ColorIndex = 3
    oCell.Font.ColorIndex = 2
    Case Else
    oCell.Interior.ColorIndex = xlColorIndexAutomatic
    oCell.Font.ColorIndex = 1
    End Select
    End If
    End Sub

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

    Re: Fine-tuning some color coding (Excel 2003)

    You have forgotten the lines
    <code>
    Next oCell
    End If
    </code>
    above End Sub.

    Using UCase means that you catch all variations of words beginning with "con": CON, COn, CoN, cON, Con, cOn, coN and con.
    If you want to add another condition for the same color, you can use
    <code>
    If UCase(oCell) Like "CON*" Or UCase(oCell) Like "DAN*" Then
    </code>
    or similar.

  5. #5
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Fine-tuning some color coding (Excel 2003)

    ...my error....thank you, Hans....it works great (and I learned some more about Excel...)..

Posting Permissions

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