Results 1 to 2 of 2
  1. #1
    Join Date
    May 2012
    Posts
    1

    Unanswered: VBA Conditional formatting code

    Hello I have attached a sheet, that I want based on certian values to change either the fill color or the font color of certain lines.

    The code I have coded works flawlessly for the 5th row, How do i modify it to work on the line that the change was made on?

    So If I were to select any line and pull the drop down from the I colum the changes would happen to that line instead of always the 5th, I understand that this is how I coded it, but I am asking how to modify my code so that it works only on the line that the change occured, I am a novice at VBA.

    Thanks in advance

    CODE:
    "
    Private Sub worksheet_change(ByVal Target As Range)

    Set Miss = Range("I3:I200")
    For Each cell In Miss

    If cell.Value = "FB-1" Then
    Range("A5,C5:I5").Select
    Range("C5").Activate
    Selection.Font.ColorIndex = 6
    End If
    If cell.Value = "FB-2" Then
    Range("A5,C5:I5").Select
    Range("C5").Activate
    Selection.Font.ColorIndex = 6
    End If
    If cell.Value = "FB-3" Then
    Range("A5,C5:I5").Select
    Range("C5").Activate
    Selection.Font.ColorIndex = 6
    End If
    If cell.Value = "FB-4" Then
    Range("A5,C5:I5").Select
    Range("C5").Activate
    Selection.Font.ColorIndex = 6
    End If

    If cell.Value = "FA-1" Then
    Range("A5,C5:I5").Select
    Range("C5").Activate
    Selection.Font.ColorIndex = 6
    End If
    If cell.Value = "FA-2" Then
    Range("A5,C5:I5").Select
    Range("C5").Activate
    Selection.Font.ColorIndex = 6
    End If
    If cell.Value = "FA-3" Then
    Range("A5,C5:I5").Select
    Range("C5").Activate
    Selection.Font.ColorIndex = 6
    End If
    If cell.Value = "FA-4" Then
    Range("A5,C5:I5").Select
    Range("C5").Activate
    Selection.Font.ColorIndex = 6
    End If
    If cell.Value = "FA-5" Then
    Range("A5,C5:I5").Select
    Range("C5").Activate
    Selection.Font.ColorIndex = 6
    End If
    If cell.Value = "FA-6" Then
    Range("A5,C5:I5").Select
    Range("C5").Activate
    Selection.Font.ColorIndex = 6
    End If
    If cell.Value = "FA-7" Then
    Range("A5,C5:I5").Select
    Range("C5").Activate
    Selection.Font.ColorIndex = 6
    End If
    If cell.Value = "F-USA" Then
    Range("A5,C5:I5").Select
    Range("C5").Activate
    Selection.Font.ColorIndex = 6
    End If

    If cell.Value = "FT-1" Then
    Range("A5,C5:I5").Select
    Range("C5").Activate
    Selection.Font.ColorIndex = 6
    End If
    If cell.Value = "FT-2" Then
    Range("A5,C5:I5").Select
    Range("C5").Activate
    Selection.Font.ColorIndex = 6
    End If
    If cell.Value = "FT-3" Then
    Range("A5,C5:I5").Select
    Range("C5").Activate
    Selection.Font.ColorIndex = 6
    End If
    If cell.Value = "FT-4" Then
    Range("A5,C5:I5").Select
    Range("C5").Activate
    Selection.Font.ColorIndex = 6
    End If
    If cell.Value = "FT-5" Then
    Range("A5,C5:I5").Select
    Range("C5").Activate
    Selection.Font.ColorIndex = 6
    End If

    If cell.Value = "AF-1" Then
    Range("A5,C5:I5").Select
    Range("C5").Activate
    Selection.Font.ColorIndex = 15
    End If
    If cell.Value = "AF-2" Then
    Range("A5,C5:I5").Select
    Range("C5").Activate
    Selection.Font.ColorIndex = 15
    End If
    If cell.Value = "AF-3" Then
    Range("A5,C5:I5").Select
    Range("C5").Activate
    Selection.Font.ColorIndex = 15
    End If
    If cell.Value = "AF-4" Then
    Range("A5,C5:I5").Select
    Range("C5").Activate
    Selection.Font.ColorIndex = 15
    End If
    If cell.Value = "AF-5" Then
    Range("A5,C5:I5").Select
    Range("C5").Activate
    Selection.Font.ColorIndex = 15
    End If
    If cell.Value = "AF-6" Then
    Range("A5,C5:I5").Select
    Range("C5").Activate
    Selection.Font.ColorIndex = 15
    End If
    If cell.Value = "AF-7" Then
    Range("A5,C5:I5").Select
    Range("C5").Activate
    Selection.Font.ColorIndex = 15
    End If

    If cell.Value = "SUP" Then
    Range("A5,C5:I5").Select
    Range("C5").Activate
    Selection.Font.ColorIndex = 4
    End If

    If cell.Value = "UNSUP" Then
    Range("A5,C5:I5").Select
    Range("C5").Activate
    Selection.Font.ColorIndex = 53
    End If

    Next

    Set Prio = Range("B3:B200")
    For Each cell In Prio

    If cell.Value = "1" Then
    cell.Interior.ColorIndex = 6
    cell.Font.ColorIndex = 1
    End If
    If cell.Value = "2" Then
    cell.Interior.ColorIndex = 33
    cell.Font.ColorIndex = 1
    End If
    If cell.Value = "3" Then
    cell.Interior.ColorIndex = 7
    cell.Font.ColorIndex = 1
    End If
    If cell.Value = "4" Then
    cell.Interior.ColorIndex = 45
    cell.Font.ColorIndex = 1
    End If

    Next

    Set Ops = Range("J3:J200")
    For Each cell In Ops

    If cell.Value = "1" Then
    Range("A5,C5:I5,J5").Select
    Range("C5").Activate
    Selection.Font.ColorIndex = 3
    End If

    Next

    Set Maint = Range("K3:K200")
    For Each cell In Maint

    If cell.Value = "1" Then
    Range("A5,C5:I5").Select
    Range("C5").Activate
    Selection.Font.ColorIndex = 7
    End If

    Next

    Set Wx = Range("L3:L200")
    For Each cell In Wx

    If cell.Value = "1" Then
    Range("A5,C5:I5").Select
    Range("C5").Activate
    Selection.Font.ColorIndex = 7
    End If

    Next

    End Sub

    "
    Attached Files Attached Files
    Last edited by clockstick; 05-04-12 at 16:28.

  2. #2
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Hi

    I've tried to simplify it a bit (although no gurantee that I have fully understood your requirements).
    Code:
    Private Sub Worksheet_change(ByVal Target As Range)
        Dim cel As Range
        Dim iRow As Long
            
        iRow = Target.Row
        
        For Each cel In Range("I3:I200")
            Select Case cel.Value
                Case Is = "FB-1", "FB-2", "FB-3", "FB-4", "FB-5"
                    Range("A" & iRow & ",C" & iRow & ":I" & iRow).Font.ColorIndex = 6
                Case Is = "FA-1", "FA-2", "FA-3", "FA-4", "FA-5", "FA-6", "FA-7"
                    Range("A" & iRow & ",C" & iRow & ":I" & iRow).Font.ColorIndex = 6
                Case Is = "FA-1", "FA-2", "FA-3", "FA-4", "FA-5", "FA-6", "FA-7"
                    Range("A" & iRow & ",C" & iRow & ":I" & iRow).Font.ColorIndex = 6
                Case Is = "FT-1", "FT-2", "FT-3", "FT-4", "FT-5"
                    Range("A" & iRow & ",C" & iRow & ":I" & iRow).Font.ColorIndex = 6
                Case Is = "AF-1", "AF-2", "AF-3", "AF-4", "AF-5", "AF-6", "AF-7"
                    Range("A" & iRow & ",C" & iRow & ":I" & iRow).Font.ColorIndex = 15
                Case Is = "SUP"
                    Range("A" & iRow & ",C" & iRow & ":I" & iRow).Font.ColorIndex = 4
                Case Is = "UNSUP"
                    Range("A" & iRow & ",C" & iRow & ":I" & iRow).Font.ColorIndex = 53
                'etc ....
            End Select
        Next
    End Sub
    If this is a bit advanced you can find more info on the Select Case construct in VBA help.

    HTH



    MTB

Posting Permissions

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