run macro when Right Arrow key pressed

[mikedee1400]

Hi. Would appreciate some help on creating a macro.

I have Sheet2 containing Supplier data in 2 columns - SupplierTAxCode and SupplierName (unsorted)

Sheet1 contains the Expenses section.

In Sheet1, column E6 to E1000, after inputting each SupplierTAxCode and pressing the RIGHT ARROW key, I would like to automatically get the SupplierName in column F6 to F1000 - from the corresponding data in Sheet2

In the event that the input in any of the cells in E6 to E1000 does not exist in SupplierTAxCode in Sheet2, would it be possible at the same time, to change the background color of the adjacent cell (SupplierName) in column F6 to F1000 at the same time - as a reminder to add those to the Supplier data in Sheet2 at a later date.

I could use Index Match and Conditional Formatting, but am concerned about the file sizes.

Many thanks in advance.

Mike

 

[Andrew Poulsom]

You could use the Worksheet_Change event procedure, testing for a change in E6:E1000.

But I don't know why you are concerned about using formulas and conditional formatting. From a quick test putting the same formula and conditional formatting in 1000 cells only increased the file size by about 70kb.

 

[mikedee1400]

Hi Andrew. Thanks for your quick reply.

My Index Match and Conditional Formatting bumped up the file size by 200kb (it's not a lot, but I tend to keep files small to view on my mobile). Maybe it's because I use the If ISNA argument in the Index Match.

Would appreciate more info on how I could use the Worksheet_change event procedure instead.

Many thanks

Mike

 

[Andrew Poulsom]

Try:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim cell As Range
    If Application.Intersect(Target, Range("E6:E1000")) Is Nothing Then Exit Sub
    For Each cell In Target
        With cell
            If WorksheetFunction.CountIf(Worksheets("Sheet2").Range("A:A"), cell.Value) = 0 Then
                .Offset(, 1).Interior.ColorIndex = 3
            Else
                .Offset(, 1).Value = WorksheetFunction.VLookup(.Value, Worksheets("Sheet2").Range("A:B"), 2, False)
                .Offset(, 1).Interior.ColorIndex = xlNone
            End If
        End With
    Next cell
End Sub

 

[mikedee1400]

Brilliant. Works like a charm.

Many thanks

Mike

 

[mikedee1400]

Hi Andrew,

Just out of interest - I understand that using vlookup only references columns to the right of "VLOOKUP column".

Is there any way your example could be changed so that I could use "Index & Match" instead - preferable without the #N/A (in case a match isn't found) - but the #N/A doesn't matter if it's too complicated.

Thanks again

Mike

Try:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim cell As Range
    If Application.Intersect(Target, Range("E6:E1000")) Is Nothing Then Exit Sub
    For Each cell In Target
        With cell
            If WorksheetFunction.CountIf(Worksheets("Sheet2").Range("A:A"), cell.Value) = 0 Then
                .Offset(, 1).Interior.ColorIndex = 3
            Else
                .Offset(, 1).Value = WorksheetFunction.VLookup(.Value, Worksheets("Sheet2").Range("A:B"), 2, False)
                .Offset(, 1).Interior.ColorIndex = xlNone
            End If
        End With
    Next cell
End Sub

 

[Andrew Poulsom]

What column do you want to INDEX and what column do you want to MATCH?

 

[mikedee1400]

Similar to my original case - but assume that Columns A and B in Sheet2 (the Supplier data) were reversed. Thus the new Column A = SupplierName and Column B = SupplierTaxCode

in brief:
Sheet 1 (the Expenses sheet) stays exactly as before i.e. input in E6:E1000 (SupplierTaxCode) and automatic output in F6:F1000 (SupplierName)

Using VBA, to Index Match the input in Sheet1 range E6:E1000 (SupplierTaxCode) with column B in Sheet2. Upon pressing Enter key, the result (SupplierName) to show in Sheet1 range F6:F1000 (based on Sheet2 Column A).

In case the input in range E6:E1000 does not exist, the corresponding cell in F6:F1000 to be colored - as a reminder to add to the data table for the next day's workbook.

I have asked for an example of Index Match in VBA - so that I could apply it in other workbooks where columns weren't placed properly when the files were created.

Thanks again

Mike

 

[Andrew Poulsom]

Using INDEX/MATCH instead of VLOOKUP my original code would be:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim cell As Range
    If Application.Intersect(Target, Range("E6:E1000")) Is Nothing Then Exit Sub
    For Each cell In Target
        With cell
            If WorksheetFunction.CountIf(Worksheets("Sheet2").Range("A:A"), cell.Value) = 0 Then
                .Offset(, 1).Interior.ColorIndex = 3
            Else
                .Offset(, 1).Value = WorksheetFunction.Index(Worksheets("Sheet2").Range("B:B"), WorksheetFunction.Match(.Value, Worksheets("Sheet2").Range("A:A"), False))
                .Offset(, 1).Interior.ColorIndex = xlNone
            End If
        End With
    Next cell
End Sub

 

[mikedee1400]

Thanks once again Andrew. You've been a great help !

Best regards

Mike