Inventory Macro / Conditional Formatting

Question

I'm taking on a project in which I've built a map of locations in which house inventory. On one sheet is the map and on another sheet would be inventory that hasnt moved in X number of days pulled from a query. What I'm wanting to do is highlight the location on the map with a red color that corresponds to the location containing old inventory.

Examples of each:

Map_Locations

Old_Inventory_With_Location

I attempted to do this using Conditional formatting but couldnt come up with a formula to accomplish this, I also wrote the following code hoping for the same results with no success (running this causes excel to crash):

Sub Highlight()

Dim Locations As Range
Dim Old_Inv As Range
Dim MyRange As Range
Dim MyRange2 As Range

Set Locations = Worksheets("Sheet3").Range("C4:CD71")
Set Old_Inv = Worksheets("Sheet2").Range("C2:C20000")

For Each MyRange In Locations
    For Each MyRange2 In Old_Inv
        If MyRange.Value = MyRange2.Value Then
            With Selection.Interior
                .Pattern = xlSolid
                .PatternColorIndex = xlAutomatic
                .ThemeColor = xlThemeColorDark2
            End With

        End If

        Next MyRange2
        Next MyRange

End Sub

Please note I have very little experience using VBA, sorry if this is way off the mark. Any thoughts or suggestions would be much appreciated

Instead of having nested loops, just loop through your Locations range and use the Match function (use WorksheetFunctions to invoke it) to check if it exists in Old_Inv. If Match returns a number, change the colour to red — Zac

FunThomas FunThomas · Accepted Answer · 2018-09-24T15:42:29

For conditional formatting:

Select cell C4 of your location sheet
Open Conditional Formatting, Manage Rules, New Rule
Select "Use a formula to determine with cells to format" (last option)
Type the following formula into the box "format values where this formula is true" (note that you might have to change the formula if you have different language settings)
=NOT(ISNA(VLOOKUP(C4, Sheet2!$C$2:$C$2000,1,FALSE)))
Click the format button to select the color you want (on the Fill-tab)
Press Ok 2 times and enter =$C$4:$CD$71 in field "Applies to"

Explanation of the formula:

VLOOKUP will search the value of cell C4 (1st parameter) within the given range of Sheet2 (2nd parameter). It returns this value (the 3rd parameter, 1 in the formula tells this) if the exact value is found (the 4th parameter must be FALSE).

If the value is not found, excel returns #NA which is checked by the surrounding IsNA-function. As this would format the cells where the value is not in the list, surround the whole stuff with the NOT- function which will revert the result.

Inventory Macro / Conditional Formatting

1 Answers