VBA to highlight row and limit

[ANE0709]

Not sure how to word that but i started writing a change event that highlights the row if certain conditions are met. im still very much learning change events and highlighting using VBA so im not sure how to work this, what i have is a start but probably far off. Basically the conditions i am trying to meet are:

Highlight columns A-AP if:
if the value of column C = C then highlight row green (color 5296274)
if column I <> 0 then highlight yellow (color index 6)
if column I and column L <> 0 then highlight yellow (color index 6)
if column J and column L <> 0 then highlight purple (color index 39)

Should there be a conflict with column C, I, and J i want column C to take priority. This shouldnt ever happen unless there is user error but just in case.

Columns C and I-P are all manual entry by the user.

 

[johnnyL]

What code have you come up with so far?

 

[ANE0709]

What code have you come up with so far?

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.CountLarge > 1 Then Exit Sub
If Target.Column = 3 And Target.Row > 3 And Target.Value = "C" Then
With Target
.EntireRow.Interior.Color = 5296274 <<<<this needs to limit to only column A-P
End With
End If
If Target.Column = 9 And Target.Column = 12 And Target.Row > 3 And Target.Value <> 0 Then
With Target
.EntireRow.Interior.ColorIndex = 6
End With
End If
If Target.Column = 9 And Target.Row > 3 And Target.Value <> 0 Then
With Target
.EntireRow.Interior.ColorIndex = 6
End With
End If
If Target.Column = 10 And Target.Column = 12 And Target.Row > 3 And Target.Value <> 0 Then
With Target
.EntireRow.Interior.ColorIndex = 39
End With
End If
End Sub

 

[johnnyL]

The following should do what you asked for:

Private Sub Worksheet_Change(ByVal Target As Range)
'
    If Target.CountLarge > 1 Then Exit Sub
'
    Select Case Target.Column
        Case 3                                                                              ' Check for Column C change
            If Target.Row > 3 And Target.Value = "C" Then Range("A" & Target.Row & _
                    ":P" & Target.Row).Interior.Color = 5296274
'
        Case 9                                                                              ' Check for Column I change
            If Range("C" & Target.Row).Value <> "C" Then
                If Target.Row > 3 And Target.Value <> 0 Then Range("A" & Target.Row & _
                        ":P" & Target.Row).Interior.ColorIndex = 6
            End If
'
        Case 10                                                                             ' Check for Column J change
            If Range("C" & Target.Row).Value <> "C" Then
                If Target.Row > 3 And Target.Value <> 0 Then
                    If Range("L" & Target.Row).Value <> 0 Then Range("A" & Target.Row & _
                            ":P" & Target.Row).Interior.ColorIndex = 39
                End If
            End If
'
        Case 12                                                                             ' Check for Column L change
            If Range("C" & Target.Row).Value <> "C" Then
                If Target.Row > 3 And Target.Value <> 0 Then
                    If Range("I" & Target.Row).Value <> 0 Then
                        Range("A" & Target.Row & ":P" & Target.Row).Interior.ColorIndex = 6
                    ElseIf Range("J" & Target.Row).Value <> 0 Then
                        Range("A" & Target.Row & ":P" & Target.Row).Interior.ColorIndex = 39
                    End If
                End If
            End If
    End Select
End Sub

 

[Joe4]

Just so you are aware, you can do this without VBA simply by using Conditional Formatting.
Perhaps there is some reason why you need it to be VBA, but just thought I would mention that.

 

[johnnyL]

Here is what 'should' be a faster version of vba code because it reduces a bunch of needless checks to 'cut to the chase' so to speak:

Private Sub Worksheet_Change(ByVal Target As Range)
'
    If Target.CountLarge > 1 Then Exit Sub
'
    If Target.Column = 3 And Target.Row > 3 And Target.Value = "C" Then                                         ' If C4:C* changed to "C" then ...
        Range("A" & Target.Row & ":P" & Target.Row).Interior.Color = 5296274                                    '   Set the row range to green
        Exit Sub                                                                                                '   Exit the Sub
    End If
'
    If Target.Column = 9 Or Target.Column = 10 Or Target.Column = 12 Then                                       ' If Column I,J, or L cell changed then ...
        Select Case Range("C" & Target.Row).Value                                                               '   Check Column C value of same row
            Case Is <> "C"                                                                                      '       If Column C value <> "C" then ...
                Select Case Target.Row                                                                          '       Check changed row #
                    Case Is > 3                                                                                 '       If Row # > 3 then ...
                        Select Case Target.Value                                                                '       Check value that changed
                            Case Is <> 0                                                                        '       If Value <> 0 then ...
                                Select Case Target.Column                                                       '       Check which column changed
                                    Case 9                                                                      '       If Column I changed then ...
                                        Range("A" & Target.Row & ":P" & Target.Row).Interior.ColorIndex = 6     '       Set the row range to yellow
                                    Case 10                                                                     '       If Column J changed then ...
                                        If Range("L" & Target.Row).Value <> 0 Then Range("A" & Target.Row & _
                                                ":P" & Target.Row).Interior.ColorIndex = 39                     ' If Column L row value <> 0 then
'                                                                                                               '       Set the row range to purple
                                    Case 12                                                                     '       If Column L changed then ...
                                        If Range("I" & Target.Row).Value <> 0 Then                              ' If Column I row value <> 0 then ...
                                            Range("A" & Target.Row & ":P" & Target.Row).Interior.ColorIndex = 6 '       Set the row range to yellow
                                        ElseIf Range("J" & Target.Row).Value <> 0 Then                          ' Else If Column J row value <> 0 then ...
                                            Range("A" & Target.Row & ":P" & Target.Row).Interior.ColorIndex = 39    '   Set the row range to purple
                                        End If
                                End Select
                        End Select
                End Select
        End Select
    End If
End Sub

 

[ANE0709]

The following should do what you asked for:

Private Sub Worksheet_Change(ByVal Target As Range)
'
    If Target.CountLarge > 1 Then Exit Sub
'
    Select Case Target.Column
        Case 3                                                                              ' Check for Column C change
            If Target.Row > 3 And Target.Value = "C" Then Range("A" & Target.Row & _
                    ":P" & Target.Row).Interior.Color = 5296274
'
        Case 9                                                                              ' Check for Column I change
            If Range("C" & Target.Row).Value <> "C" Then
                If Target.Row > 3 And Target.Value <> 0 Then Range("A" & Target.Row & _
                        ":P" & Target.Row).Interior.ColorIndex = 6
            End If
'
        Case 10                                                                             ' Check for Column J change
            If Range("C" & Target.Row).Value <> "C" Then
                If Target.Row > 3 And Target.Value <> 0 Then
                    If Range("L" & Target.Row).Value <> 0 Then Range("A" & Target.Row & _
                            ":P" & Target.Row).Interior.ColorIndex = 39
                End If
            End If
'
        Case 12                                                                             ' Check for Column L change
            If Range("C" & Target.Row).Value <> "C" Then
                If Target.Row > 3 And Target.Value <> 0 Then
                    If Range("I" & Target.Row).Value <> 0 Then
                        Range("A" & Target.Row & ":P" & Target.Row).Interior.ColorIndex = 6
                    ElseIf Range("J" & Target.Row).Value <> 0 Then
                        Range("A" & Target.Row & ":P" & Target.Row).Interior.ColorIndex = 39
                    End If
                End If
            End If
    End Select
End Sub

this one is actually pretty wonderful and works beautifully! does is exactly what i was looking for add added that missing layer i still need to learn. Thanks so much.

 

[ANE0709]

Just so you are aware, you can do this without VBA simply by using Conditional Formatting.
Perhaps there is some reason why you need it to be VBA, but just thought I would mention that.

Thanks Joe4. yes i am aware of the conditional formatting. the file as a whole was originally being performed manually and took over an hour to process everything. Ive managed to knock it down to under 5 minutes with VBA and haven't had a lot of success with conditional formatting when applied to a template file. JohnnyL's first suggestion worked great and was easily laid out for me to study and learn from for future projects.

 

[johnnyL]

JohnnyL's first suggestion worked great and was easily laid out for me to study and learn from for future projects.

My second suggestion didn't work? It should, and it theoretically should work faster than my first suggestion.

 

[bebo021999]

if column I <> 0 then highlight yellow (color index 6)
if column I and column L <> 0 then highlight yellow (color index 6)

Is it only one criteria for column I<>0 only (disregard column L)?
Also, with any change in column C or I:L, the code will check from 1st to last priority.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim U As Range
Set U = Union(Range("C:C"), Range("I:L"))
If Intersect(Target, U) Is Nothing Then Exit Sub
With Range(Cells(Target.Row, "A"), Cells(Target.Row, "P")).Interior
    Select Case True
        Case U(Target.Row, 1) = "C" ' 1st priority
            .Color = 5296274
        Case U(Target.Row, 7) <> 0 '' 2nd priority
            .ColorIndex = 6
        Case U(Target.Row, 8) <> 0 And U(Target.Row, 10) <> 0 ' 3rd priority
            .ColorIndex = 39
        Case Else
            .Color = xlNone 'other
    End Select
End With
End Sub