PDA

View Full Version : Solved: Iserror + Vlookup to color cells where value is present in a list



MisterNate
01-18-2011, 11:40 AM
Hi, All.

So, I know what I want to do, have an idea how to do it, but not sure where to start.

I have a list of stores that are included in a lobby display program which includes store numbers.

I also have a list of order adjustment recommendations that my team uses to adjust orders, which also includes store numbers. But, not all stores included in the recommendation list have lobby displays.

What I want to do is color the cells containing the store numbers in the recommendation list that are found in the lobby display list, and leave the ones that are not in the lobby display list, and I want this to happen as a part of the recommendation list preparation macro.

I have thought a way to do this is create a loop that starts on the first line of the recommendation list, and goes down until the store number cell is blank, that would look a little like this (forgive the half code/half sentence format):

Do Until store# = ""

If(iserror(vlookup(store#, lobby display range, etc) then loop counter = loop counter + 1

Else: Range(store#) Color = blue, loop counter + 1

Loop

Any help on how to actually code this would be hugely appreciated - I know how to reference the relevant cell locations, just need to know how the iserror and vlookup check would look in VBA.

Thanks a ton in advance - please let me know if there's anything further required for clarification.

Cheers!
Nate.

MisterNate
01-18-2011, 11:59 AM
Ok, so after a little more messing around, I figured it out:

Sub HighlightFeatureDisplayStores()
Dim varRow As Long

varRow = 2

Sheets("Truck Report").Select

Range("B2").Select

Do While Cells(varRow, 2).Value <> ""

If Not IsError(Application.VLookup(Range("B" & varRow), Sheets("FD Stores").Range("A:A"), 1, 0)) Then
Range("B" & varRow).Interior.ColorIndex = 7
varRow = varRow + 1

Else: varRow = varRow + 1

End If

Loop


End Sub

Should have messed around a little longer before bugging you all...sorry about that!

Thanks anyway... :)