conditional formatting more than 3 conditions - Page 2
MZ Tools makes life easier for the Excel VBA coder
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!

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
  •  

 

 
DMCA.com