Target Offset problems in Event

[cwalenciak]

Hello. I am trying to fill a cells interior color if a condition is not met. The issue is while the columns where I am texting the condition move, the column that I want to fill does not.

So I think it the offset that I am trying to figure out how to replace. Thanks


Private Sub Worksheet_Change(ByVal Target As Range)


If Not Intersect(Target, Range("O:R")) Is Nothing Then
If InStr(Target, "round") > 0 Or InStr(Target, "flat") > 0 Or InStr(Target, "square") > 0 Then
Application.EnableEvents = False
Target.Offset(0, 7).Interior.Color = xlNone
Target.Offset(0, 8).Interior.Color = xlNone
Application.EnableEvents = True
Else
Application.EnableEvents = False
Intersect(Target, Range("O:R")).Offset(0, 7).Interior.Color = RGB(165, 165, 165)
Intersect(Target, Range("O:R")).Offset(0, 8).Interior.Color = RGB(165, 165, 165)
Application.EnableEvents = True
End If
End If


End Sub

 

[RedBeard]

Not quite sure what you're after, but you offset using the target cell, meaning that any change will be relative to that cell.

 

[cwalenciak]

Not quite sure what you're after, but you offset using the target cell, meaning that any change will be relative to that cell.

I realize now it is unclear.

I have Columns O through R. If any of those cells meets a condition (either saying square, flat or round) then I want to leave columns U and V with no fill. If none of them meet the condition then I want to fill U and V (with the respective row) gray.

Column O /Column P/ Column Q/ Column S/ Column U /Column V
None/ none /none /none /gray Fill /gray fill (Row 1)
Flat/ none/ none/ none /No Fill /No Fill (Row 2)


Let me know if that makes since.

 

[RedBeard]

Try this:

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("O:R")) Is Nothing Then
    
    If InStr(Target, "round") > 0 Or InStr(Target, "flat") > 0 Or InStr(Target, "square") > 0 Then
    
        Cells(Target.Row, 22).Interior.Color = xlNone
        Cells(Target.Row, 23).Interior.Color = xlNone
    
    Else


        Cells(Target.Row, 22).Interior.Color = RGB(165, 165, 165)
        Cells(Target.Row, 23).Interior.Color = RGB(165, 165, 165)
        
    End If
    
End If


End Sub

 

[cwalenciak]

Thank you for shedding light on Target.Row. I modified it a little to get what I needed. Let me know if I can change anything.

Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("O:R")) Is Nothing Then
    
    If InStr(Range("O" & Target.Row), "round") > 0 and InStr(Range("P" & Target.Row), "round") > 0 and InStr(Range("P" & Target.Row), "round"") >     0 Then
    
        Range("V" & Target.Row).Interior.Color = xlNone
        Range("W" & Target.Row).Interior.Color = xlNone
    
    Else


        Range("V" & Target.Row).Interior.Color = RGB(165, 165, 165)
        Range("W" & Target.Row).Interior.Color = RGB(165, 165, 165)
        
    End If
    
End If


End Sub