CODEWITHSUNDEEP
vbaexcel
alex2002
alex2002

Reputation: 161

Color doesn't show when pasting multiple times

I would really appreciate if someone can help me to identify the problem with this code. The code is suppose to turn column O into red every time when "Spain" is inserted in column A. I.e. if A2 is "Spain" then O2 turns red. The code below does this job when I am writing "Spain" in A2. However, if I copy-paste "Spain" from A2 to A10, then only cell that will turn red in column O will be cell O2.

Why cells O3 to O10 don't turn red as well? How can I do that? Must appreciated. 

Sub columnO(d As Long)

    If Cells(d, "A") = "Spain" And Cells(d, "O") = "" Then
    Cells(d, "O").Interior.Color = RGB(255, 0, 0)
    Else
    Cells(d, "O").Interior.Color = RGB(1000, 1000, 1000)
    End If

    End Sub


Private Sub Worksheet_Change(ByVal Target As Range)

    If Not Application.Intersect(Range("A5:O10"), Target) Is Nothing Then
       columnO Target.row
    End If

Upvotes: 1

Views: 51

Answers (2)

tigeravatar
tigeravatar

Reputation: 26650

As an alternative, this can be done without any VBA by utilizing Conditional Formatting. Create a new conditional format rule using a formula and apply to the desired range (in this case $O$2:$O$10): 

=AND($A2="Spain",$O2="")

Upvotes: 4

YowE3K
YowE3K

Reputation: 23984

Change your Change event to loop through all rows in Target

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim r As Range
    If Not Application.Intersect(Range("A5:O10"), Target) Is Nothing Then
        For Each r In Application.Intersect(Range("A5:O10"), Target).Rows
           columnO r.Row
        Next
    End If
End Sub

Upvotes: 2

Related Questions