Highlight cells with multiple conditions

phiyen8902 · Jun 4th 2019

Hi everyone,
Pls help me fix this code below. I want to highlight cells with conditions as follow list

If col(I)=P then color col Q,R,S,T into green
If col(I)=P and col(Y) start with “Rev*” or “REV*” then color col Q,R,S,T into yellow
If remove value in col(Y) then col Q,R,S,T into green back
If remove value in col(I) then col Q,R,S,T nofill

Thanks for you help.

Code

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column <> 9 And Target.Column <> 25 Then Exit Sub 'only run if input date in col I and col Y
    If Target.Row <= 5 Then Exit Sub "only run from row 5
    If Not IsEmpty(Cells(Target.Row, 4)) Then Exit Sub 'only run if col D blank
    If Target.Column = 9 And Target.Value = "P" Then
        Target.Offset(0, 8).Interior.ColorIndex = 4
        Target.Offset(0, 9).Interior.ColorIndex = 4
        Target.Offset(0, 10).Interior.ColorIndex = 4
        Target.Offset(0, 11).Interior.ColorIndex = 4
    ElseIf Cells(Target.Row, 9) = "P" Or Left(Target.Row, 25),3) = "Rev" Then 'check if no
        Target.Offset(0, -8).Interior.ColorIndex = 6
        Target.Offset(0, -7).Interior.ColorIndex = 6
        Target.Offset(0, -6).Interior.ColorIndex = 6
        Target.Offset(0, -5).Interior.ColorIndex = 6
    Else
        Target.Offset(0, 8).Interior.ColorIndex = xlNone
        Target.Offset(0, 9).Interior.ColorIndex = xlNone
        Target.Offset(0, 10).Interior.ColorIndex = xlNone
        Target.Offset(0, 11).Interior.ColorIndex = xlNone
    End If
End Sub

Display More

phiyen8902 · Jun 10th 2019

Hi experts,

Pls help me with this master, Thanks in advance!

**KjBox** · Jun 10th 2019

Attach your workbook.

phiyen8902 · Jun 10th 2019

Hi KjBox,

Pls refer the attached file. Thanks for your help.

**KjBox** · Jun 10th 2019

Maybe

Code

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column <> 9 And Target.Column <> 25 Then Exit Sub
    If Target.Row <= 5 Then Exit Sub
    If Target.Count > 1 Then Exit Sub


    If Not IsEmpty(Cells(Target.Row, 4)) Then
        Cells(Target.Row, 17).Resize(, 4).Interior.ColorIndex = xlNone
        If Target.Column = 9 Then
            If Cells(Target.Row, 9) = "P" _
                And LCase(Cells(Target.Row, 25)) Like "rev*" Then 'check if no
                Cells(Target.Row, 17).Resize(, 4).Interior.Color = vbYellow
            ElseIf Cells(Target.Row, 9) = "P" Then
                Cells(Target.Row, 17).Resize(, 4).Interior.Color = vbGreen
            Else
                Cells(Target.Row, 17).Resize(, 4).Interior.ColorIndex = xlNone
            End If
        ElseIf Target = "" Then
            Cells(Target.Row, 17).Resize(, 4).Interior.Color = vbGreen
        End If
    Else
        Cells(Target.Row, 17).Resize(, 4).Interior.ColorIndex = xlNone
    End If
    
End Sub

Display More

phiyen8902 · Jun 10th 2019

Hi KjBox,

2) If col(I)=P and col(Y) start with “Rev*” or “REV*” then color col Q,R,S,T into yellow. For this condition, if I input col(I) then col(Y) => it run very well, however, if I input col(Y) first and then col(I) => it's no fill instead of highlight yellow. Would you have any solution for this issue ?

**KjBox** · Jun 10th 2019

Try

Code

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column <> 9 And Target.Column <> 25 Then Exit Sub
    If Target.Row <= 5 Then Exit Sub
    If Target.Count > 1 Then Exit Sub
    
    If Not IsEmpty(Cells(Target.Row, 4)) Then
        Cells(Target.Row, 17).Resize(, 4).Interior.ColorIndex = xlNone
        If Target.Column = 9 Then
            If Cells(Target.Row, 9) = "P" _
                And LCase(Cells(Target.Row, 25)) Like "rev*" Then 'check if no
                Cells(Target.Row, 17).Resize(, 4).Interior.Color = vbYellow
            ElseIf Cells(Target.Row, 9) = "P" Then
                Cells(Target.Row, 17).Resize(, 4).Interior.Color = vbGreen
            Else
                Cells(Target.Row, 17).Resize(, 4).Interior.ColorIndex = xlNone
            End If
        ElseIf Target = "" Then
            Cells(Target.Row, 17).Resize(, 4).Interior.Color = vbGreen
        End If
    Else
        Cells(Target.Row, 17).Resize(, 4).Interior.ColorIndex = xlNone
        If Cells(Target.Row, 9) = "P" _
            And LCase(Cells(Target.Row, 25)) Like "rev*" Then 'check if no
                Cells(Target.Row, 17).Resize(, 4).Interior.Color = vbYellow
        ElseIf Cells(Target.Row, 25) = vbNullString Then
            Cells(Target.Row, 17).Resize(, 4).Interior.ColorIndex = xlNone
        End If
    End If
    
End Sub

Display More

phiyen8902 · Jun 10th 2019

Hi KjBox,

It's also same, cant highlight yellow if I input col (Y) after.

**KjBox** · Jun 10th 2019

Try

Code

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Row < 6 Then Exit Sub
    If Target.Count > 1 Then Exit Sub
    
    If Not IsEmpty(Cells(Target.Row, 4)) Then
        Cells(Target.Row, 17).Resize(, 4).Interior.ColorIndex = xlNone
        If Target.Column = 9 Then
            If Cells(Target.Row, 9) = "P" _
                And LCase(Cells(Target.Row, 25)) Like "rev*" Then 'check if no
                Cells(Target.Row, 17).Resize(, 4).Interior.Color = vbYellow
            ElseIf Cells(Target.Row, 9) = "P" Then
                Cells(Target.Row, 17).Resize(, 4).Interior.Color = vbGreen
            Else
                Cells(Target.Row, 17).Resize(, 4).Interior.ColorIndex = xlNone
            End If
        ElseIf Target.Column = 25 Then
            If Target = vbNullString Then
                Cells(Target.Row, 17).Resize(, 4).Interior.Color = vbGreen
            ElseIf Cells(Target.Row, 9) = "P" _
                And LCase(Cells(Target.Row, 25)) Like "rev*" Then 'check if no
                    Cells(Target.Row, 17).Resize(, 4).Interior.Color = vbYellow
                ElseIf Cells(Target.Row, 25) = vbNullString Then
                    Cells(Target.Row, 17).Resize(, 4).Interior.ColorIndex = xlNone
            End If
        End I
    End If
    
End Sub

Display More

phiyen8902 · Jun 10th 2019

HI KjBox,

I'm sorry if it made you headache, but it's still highlight green eventhough i didnt input anything and the problem above is same.:(

**KjBox** · Jun 10th 2019

I over-complicated it, try this

Code

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Row < 6 Then Exit Sub
    If Target.Count > 1 Then Exit Sub
    
    If Not IsEmpty(Cells(Target.Row, 4)) Then
        Cells(Target.Row, 17).Resize(, 4).Interior.ColorIndex = xlNone
        If Target.Column = 9 Then
            If Cells(Target.Row, 9) = "P" _
                And LCase(Cells(Target.Row, 25)) Like "rev*" Then
                Cells(Target.Row, 17).Resize(, 4).Interior.Color = vbYellow
            ElseIf Cells(Target.Row, 9) = "P" Then
                Cells(Target.Row, 17).Resize(, 4).Interior.Color = vbGreen
            End If
        ElseIf Target.Column = 25 Then
            If Cells(Target.Row, 9) = "P" _
                And LCase(Cells(Target.Row, 25)) Like "rev*" Then 'check if no
                    Cells(Target.Row, 17).Resize(, 4).Interior.Color = vbYellow
            End If
        End If
    Else
        Cells(Target.Row, 17).Resize(, 4).Interior.ColorIndex = xlNone
    End If
    
End Sub

Display More

phiyen8902 · Jun 10th 2019

Hi KjBox,

It's still not

**KjBox** · Jun 10th 2019

it works for me.

Detail all the steps you take when it does not work for you. Also which row are you testing with?

phiyen8902 · Jun 10th 2019

Hi KjBox,

I test on row 9. Firstly, I remove P and Rev in col I and Y and col QRST nofill. Secondly, I input P in col I then col QRST turn green => OK. Third, I input "rev" in col Y, col QRST nofill =>it's should be yellow. Fouth, I remove P in col I then col QRST nofill => OK. Finally, I remove "rev" in col Y then col QRST turn green => it's should be nofill. It's my step to test.

**KjBox** · Jun 10th 2019

OK, I got the bit about column D wrong, try this

Code

Private Sub Worksheet_Change(ByVal Target As Range)


    If Target.Row < 6 Then Exit Sub
    If Target.Count > 1 Then Exit Sub
    
    With Cells(Target.Row, 17).Resize(, 4).Interior
        If IsEmpty(Cells(Target.Row, 4)) Then
            .ColorIndex = xlNone
            If Target.Column = 9 Or Target.Column = 25 Then
                If Cells(Target.Row, 9) = "P" Then
                    .Color = vbGreen
                    If LCase(Cells(Target.Row, 25)) Like "rev*" Then
                        .Color = vbYellow
                    End If
                End If
            End If
        Else
            .ColorIndex = xlNone
        End If
    End With
    
End Sub

Display More

phiyen8902 · Jun 10th 2019

Hi Kjbox,

It worked, it worked, a thousand thanks for your help. Thank you so so so much KjBox.

**KjBox** · Jun 10th 2019

You're welcome, pleased we got there in the end!

Have you missed that I also did a revised code for your other thread

https://www.ozgrid.com/forum/f…ta-from-another-worksheet

The new code will drastically reduce the run time of the previous code when run on your full dataset.

Highlight cells with multiple conditions

Participate now!

Share