Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 2 of 2 FirstFirst 12
Results 11 to 14 of 14

Thread: conditional formatting more than 3 conditions

  1. #11
    Board Regular
    Join Date
    Feb 2002
    Location
    Guderup, Denmark
    Posts
    288
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    if each cell in column A relates to a cell in the same row,but in column B you could use this macro. (to insert rightckick on the sheettab, choose show code and paste the code)

    Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column = 4 Then
    Select Case Target.Value
    Case 1: ci = 1
    Case 2: ci = 3
    Case 3: ci = 5
    Case 4: ci = 7
    Case 5: ci = 9
    Case 6: ci = 11
    Case 7: ci = 13
    Case 8: ci = 15
    Case 9: ci = 17
    Case 10: ci = 18
    Case Else
    End Select
    Target.Offset(0, -3).Interior.ColorIndex = ci
    End If
    End Sub

  2. #12
    Guest

    Default

    Tommy that works! but if I put a letter in column D I get type mismatch, can the be fixed? Thanks

  3. #13
    Board Regular
    Join Date
    Feb 2002
    Location
    Guderup, Denmark
    Posts
    288
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Sorry. I tested it in xl2000 with no problems.
    In xl97 this macro will do the job.
    Just added a check to see if target.value is numeric

    Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column = 4 Then
    If IsNumeric(Target.Value) Then
    Select Case Target.Value
    Case 1: ci = 1
    Case 2: ci = 3
    Case 3: ci = 5
    Case 4: ci = 7
    Case 5: ci = 9
    Case 6: ci = 11
    Case 7: ci = 13
    Case 8: ci = 15
    Case 9: ci = 17
    Case 10: ci = 18
    Case Else
    End Select
    Target.Offset(0, -3).Interior.ColorIndex = ci
    End If
    End If
    End Sub

    regards Tommy

  4. #14
    Guest

    Default

    Thanks tommy that did it!

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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