Row color based on result of a vlookup?

[exwarrior]

I need to write a macro that copies and pastes the values of all cells in the first sheet. After that is done i need to make sure that the values in column D exist in column A, and if they do not i need the background of these cells to turn red. Many thanks in advance. I will attach a sample of data for anyone who wishes to experiment with.

[Bob Phillips]

Column D and A are completely different, how could they possibly exist?

[rlv]

Assuming you meant B & D, not A & D - this snippet does not deal with the cutting and pasting, but illustrates one way to change cell colors based on whether a given value in column D is in column B


[vba]Sub Test()

Dim R As Range
Dim VResult As Variant

For Each R In ActiveSheet.Range("$D$11:$D$522")
VResult = Application.VLookup(R.Value, ActiveSheet.Range("$B$11:$B$522"), 1, False)
If VBA.IsError(VResult) Then 'D not found in B
R.Interior.ColorIndex = 3 ' set background to red
End If
Next R
End Sub
[/vba]

[exwarrior]

That worked perfectly! Can you add the ability to change the cell color back. I would like to use this to show visually the values in "D" that do not exist in "B" and be able to fix the discrepancies and run it again.

Also where can one learn advanced VBA scripting, I would eventually like to be self sufficient in this area.


**Update** I figured out how to copy and paste the values i needed.

[rlv]

This routine will clear the background colors

[VBA]Sub NormalBackGround()

Dim R As Range
Dim VResult As Variant

For Each R In ActiveSheet.Range("$D$11:$D$522")
R.Interior.ColorIndex = xlColorIndexNone ' no color
Next R
End Sub
[/VBA]

As far as learning VBA, hanging out in forums like this one is a good first step. I have always been a fan of the John Walkenbach "Power Programming with VBA" books: http://tinyurl.com/3ccq68

Mostly you just have to dive in.