Excel If Cell Is Highlighted on VLOOKUP

Question

I have two sheets in excel, one is an order form the other is a production sheet based off the order form.

I am using VLOOKUP to query the order form for the quantity of a given item ordered.

However, sometimes this quantity is highlighted on the order form, indicating that the item in question actually gets produced 2 extra amounts (for free samples).

So for example, in the production form I have:

ITEM|QUANTITY TO PRODUCE

In the order form I have:

ITEM|QUANTITY TO ORDER

I use VLOOKUP to get the match, and this works, but if a cell in QUANTITY TO ORDER is highlighted yellow, then I need the VLOOKUP value to be added by two.

How can I do this? Is there a way to do this automatically, without a macro? My client doesn't want to be manually activating things, they just expect the sheet to work.

Thank you.

Are you willing to use a User-defined function? This can be created in design-view and then called in the workbook just like any other function. — DiscipleMichael, Jun 21 '15 at 04:24

score 1 · Answer 1 · edited May 23 '17 at 12:14

VLOOKUP can't do that. What you need to do, is treat a cell's background color as data... and a cell's background color isn't data.

But... this link explains how to do that and what the implications are.

Create a workbook-scoped name (Ctrl+F3) called BackColor referring to =GET.CELL(63,OFFSET(INDIRECT("RC",FALSE),0,-1)), and then add a column immediately to the right of the column where the user highlights cells, and make that column have a formula such as =BackColor<>0 so that it contains TRUE for any highlighted cell in the column immediately to its left.

Hard-coding the extra 2 units into your formula isn't going to be maintenance-friendly, so enter that 2 in a cell somewhere and define a name called ExtraUnits for it.

Then modify your formula to

=[the original VLOOKUP]+IF([lookup the BackColor Boolean], ExtraUnits, 0)

This will add ExtraUnits to the looked up units, for all highlighted cells.

The only drawback is that, as I said above, a cell's background color isn't data as far as Excel is concerned, so your user must trigger a recalculation - just changing cells' background color will not do that, but pressing F9 will.

If the column has to be 6 units away to the right from where the person highlights cell, instead of right next to it, how would that formula look? I tried `=GET.CELL(63,OFFSET(INDIRECT("RC",FALSE),-6,0))` but that did not seem to work. — Summer Developer, Jun 21 '15 at 07:34

score 1 · Answer 2 · answered Jun 21 '15 at 05:06

The below code was found at http://www.mrexcel.com/forum/excel-questions/215415-formula-check-if-cell-highlighted.html

Function CellColorIndex(InRange As Range, Optional _ 
    OfText As Boolean = False) As Integer
'
' This function returns the ColorIndex value of a the Interior 
' (background) of a cell, or, if OfText is true, of the Font in the cell.
'
Application.Volatile True
If OfText = True Then
    CellColorIndex = InRange(1,1).Font.ColorIndex
Else
    CellColorIndex = InRange(1,1).Interior.ColorIndex
End If

End Function

To use the function:

=IF(CELLCORINDEX(A1,FALSE)>0,1,0)

This lets you check the color of the cell , or the text. But you will need to use the Index-match code found here http://www.mrexcel.com/forum/excel-questions/447723-vlookup-returns-cell-address.html in order to match it up.

Also, like the above answer states, highlighting a cell doesn't count as a data change, so even though you can get this info without a macro, if someone updates the cell's highlight status, it will not update the cells using this formula unless automatically.

Sounds like you may need to rethink the Highlighting being the trigger of the +2 samples. I'm with the above answer that recommends adding a column maybe True/False or Yes/No that is checked to see if they get samples.

Okay I will try this and get back to you, but unfortunately I can't really change the highlighting being the way to trigger the sample as that as how they have operated for some time now. :( — Summer Developer, Jun 21 '15 at 06:53

score 0 · Accepted Answer · answered Jun 21 '15 at 08:25

What I did is this:

I created a user defined function:

Function getRGB3(rcell As Range, Optional opt As Integer) As Long
    Dim C As Long
    Dim R As Long
    Dim G As Long
    Dim B As Long

    C = rcell.Interior.Color
    R = C Mod 256
    G = C \ 256 Mod 256
    B = C \ 65536 Mod 256

    If opt = 1 Then
        getRGB3 = R
    ElseIf opt = 2 Then
        getRGB3 = G
    ElseIf opt = 3 Then
        If B <> 0 Then
        B = -2
        End If
        getRGB3 = B + 2
    Else
        getRGB3 = C
    End If
End Function

This made it so all the highlighted cells (in yellow) got a value of 2 when referred to, so on the order form it goes like ITEM|QUANTITY TO ORDER|CUSTOM FUNCTION VALUE| and the third column (custom function) is 2 for each corresponding yellow cell next to it, if not, it is just zero.

Then I do a second VLOOKUP to add the CUSTOM FUNCTION VALUE to the original, and then I have added two. :)

Excel If Cell Is Highlighted on VLOOKUP

3 Answers3