Excel error 1004, Highlight adjecent cells gray when yes

Question

I'm trying to put a macro where I select Yes in a row, the next cells are grayed out. So I have got couple of yes columns, but for the life of me can't figure/fix out the error

Error
Runtime Error 1004
Application-defined or object-defied error

Code

Private Sub Worksheet_Change(ByVal Target As Range)

If ActiveCell.Column = 5 Then

    Set r = Range(ActiveCell.Offset(0, 1), ActiveCell.Offset(0, 7))

    If Target.Value = "Yes" Or Target.Value = "YES" Then
        r.Interior.Color = RGB(192, 192, 192)
    Else
        r.Interior.Color = xlNone
    End If
End If

 If ActiveCell.Column = 7 Then

    Set s = Range(ActiveCell.Offset(0, 1), ActiveCell.Offset(0, 3))

    If Target.Value = "Yes" Or Target.Value = "YES" Then
        s.Interior.Color = RGB(192, 192, 192)
    Else
        s.Interior.Color = xlNone
    End If
 End If
End Sub

[Worth A Read](http://stackoverflow.com/questions/13860894/ms-excel-crashes-when-vba-code-runs/13861640#13861640) — Siddharth Rout, May 13 '15 at 07:58

score 1 · Answer 1 · answered May 13 '15 at 04:18

1

ActiveCell is not the cell that changed. Target is. You need to replace all of the references to ActiveCell to Target and adjust the offsets accordingly.

answered May 13 '15 at 04:18

Comintern

21,855
5
33
80

Hi, changed them to `target`, but i still get the same error – djEthen May 13 '15 at 04:22
Did you change all *6* ActiveCell to Target? – May 13 '15 at 04:54
Changed all of them, found the issue though, it was using conditional highlighting so that's what was conflicting with the macro – djEthen May 14 '15 at 01:00

score 1 · Accepted Answer · answered May 13 '15 at 04:51

Changing ActiveCell to Target is a good start. You should also compensate for more than a single target cell in case a block of values is pasted into a range overlapping columns E and/or G.

Private Sub Worksheet_Change(ByVal Target As Range)

    If Not Intersect(Target, Union(Columns(5), Columns(7))) Is Nothing Then
        On Error GoTo Fìn
        Application.EnableEvents = False
        Dim o As Range, t As Range
        For Each t In Intersect(Target, Union(Columns(5), Columns(7)))
            Select Case t.Column
                Case 5
                    Set o = t.Offset(0, 1).Resize(1, 7)
                Case 7
                    Set o = t.Offset(0, 1).Resize(1, 3)
            End Select
            If LCase(t.Value) = "yes" Then
                o.Interior.Color = RGB(192, 192, 192)
            Else
                o.Interior.Pattern = xlNone
            End If
        Next t
    End If

Fìn:
    Set o = Nothing
    Application.EnableEvents = True

End Sub

I've only set the range of the cells to receive the fill color first. Note that there does seem to be a crossover logic issue where if E5 receives a Yes and G5 receives a No then only K5:L5 will be grey. I made the comparison to yes non-case-sensitive.

Errors are covered by a safe exit.

Thanks for the code, and i didn't know about the error exit so now i know — djEthen, May 14 '15 at 01:01

score 0 · Answer 3 · answered May 20 '15 at 01:39

0

Found the issue though, it was using conditional highlighting so that's what was conflicting with the macro

answered May 20 '15 at 01:39

djEthen

29
1
1
7

Excel error 1004, Highlight adjecent cells gray when yes

3 Answers3