Results 1 to 5 of 5
  1. #1
    5 Star Lounger
    Join Date
    Jul 2002
    Location
    Hatsukaichi, Hiroshima, Japan
    Posts
    904
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Conditional Formatting - more than three options? (Excel 97)

    I have ten columns of text on one worksheet named wordlist. Each cell has one different word. On another worksheet (named "keywords") I have more words. I want to colour the background of each cell on the keywords sheet containing a matching word according to the column the word is in on the wordlist sheet: eg

    range a1:a80 - red
    range b1:b80 - orange
    range c1:c80 - yellow
    range d1:d80 - green

    And so on. Excel 97 only allows me to set three conditions but I need 10. I searched through some of the posts and think I can apply a macro to do this but I have no real idea about the code or how to apply it to a worksheet. I'd be grateful for some help.

    Thanks and best wishes,

    Chris (Hunt)

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

    Re: Conditional Formatting - more than three options? (Excel 97)

    Activate the Keywords sheet.
    Right-click the sheet tab.
    Select View Code from the popup menu.
    Copy/paste the following code into the code module:
    <code>
    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim oCell As Range
    Dim oFound As Range

    On Error GoTo ErrHandler

    If Not Intersect(Range("A110"), Target) Is Nothing Then
    Application.EnableEvents = False
    For Each oCell In Intersect(Range("A110"), Target).Cells
    Set oFound = Worksheets("WordList").Range("A1:J80").Find( _
    What:=oCell, LookIn:=xlValues, LookAt:=xlWhole)
    If oFound Is Nothing Then
    oCell.Interior.ColorIndex = xlColorIndexNone
    Else
    Select Case oFound.Column
    Case 1
    oCell.Interior.Color = RGB(255, 0, 0)
    Case 2
    oCell.Interior.Color = RGB(255, 128, 0)
    Case 3
    oCell.Interior.Color = RGB(255, 255, 0)
    Case 4
    oCell.Interior.Color = RGB(0, 255, 0)
    Case 5
    oCell.Interior.Color = RGB(128, 255, 0)
    Case 6
    oCell.Interior.Color = RGB(0, 128, 255)
    Case 7
    oCell.Interior.Color = RGB(128, 0, 128)
    Case 8
    oCell.Interior.Color = RGB(255, 0, 255)
    Case 9
    oCell.Interior.Color = RGB(0, 0, 255)
    Case 10
    oCell.Interior.Color = RGB(0, 255, 255)
    Case Else
    oCell.Interior.ColorIndex = xlColorIndexNone
    End Select
    End If
    Next oCell
    End If

    ExitHandler:
    Application.EnableEvents = True
    Exit Sub

    ErrHandler:
    MsgBox Err.Description, vbExclamation
    Resume ExitHandler
    End Sub
    </code>
    I have assumed that you'll enter keywords in A110. You can change the range as needed.
    Instead of using RGB colors, you can set the ColorIndex to a number between 1 and 56.

  3. #3
    5 Star Lounger
    Join Date
    Jul 2002
    Location
    Hatsukaichi, Hiroshima, Japan
    Posts
    904
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Conditional Formatting - more than three optio

    Thanks Hans, that code looks very useful. What I want to do is to enter/change words on the wordlist sheet and have the formatting applied to the words on the keywords sheet. Is that possible?

    Best wishes,

    Chris

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

    Re: Conditional Formatting - more than three optio

    Activate the Wordlist sheet.
    Right-click the sheet tab and select View Code from the popup menu.
    Enter or copy/paste the following code:
    <code>
    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim oCell As Range
    Dim oFound As Range

    On Error GoTo ErrHandler

    If Not Intersect(Range("A1:J80"), Target) Is Nothing Then
    Application.EnableEvents = False
    For Each oCell In Worksheets("Keywords").Range("A110").Cells
    Set oFound = Range("A1:J80").Find( _
    What:=oCell, LookIn:=xlValues, LookAt:=xlWhole)
    If oFound Is Nothing Then
    oCell.Interior.ColorIndex = xlColorIndexNone
    Else
    Select Case oFound.Column
    Case 1
    oCell.Interior.Color = RGB(255, 0, 0)
    Case 2
    oCell.Interior.Color = RGB(255, 128, 0)
    Case 3
    oCell.Interior.Color = RGB(255, 255, 0)
    Case 4
    oCell.Interior.Color = RGB(0, 255, 0)
    Case 5
    oCell.Interior.Color = RGB(128, 255, 0)
    Case 6
    oCell.Interior.Color = RGB(0, 128, 255)
    Case 7
    oCell.Interior.Color = RGB(128, 0, 128)
    Case 8
    oCell.Interior.Color = RGB(255, 0, 255)
    Case 9
    oCell.Interior.Color = RGB(0, 0, 255)
    Case 10
    oCell.Interior.Color = RGB(0, 255, 255)
    Case Else
    oCell.Interior.ColorIndex = xlColorIndexNone
    End Select
    End If
    Next oCell
    End If

    ExitHandler:
    Application.EnableEvents = True
    Exit Sub

    ErrHandler:
    MsgBox Err.Description, vbExclamation
    Resume ExitHandler
    End Sub
    </code>
    Adjust the ranges mentioned as needed.

  5. #5
    5 Star Lounger
    Join Date
    Jul 2002
    Location
    Hatsukaichi, Hiroshima, Japan
    Posts
    904
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Conditional Formatting - more than three optio

    Thank you, thank you, Hans, that is brilliant! It works just as I need it to. I'm making words lists for Japanese Children to practise and learn to read English and this will be a big help.

    Best wishes,

    Chris

Posting Permissions

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