How to use Lookup in vba

[largeselection]

I'm trying to set the background color of a square shape based on the color in another cell (the background is not set by conditional formatting).

So I have in the beginning:

With Selection.ShapeRange.Fill
.Visible = msoTrue
.ForeColor.RGB =

I'm trying to figure out what to put after the =.

I have a set of data and an index. So in my index table it says if the value is between 50-60, then it should be in the "MID" bucket. I can do a lookup in the worksheet (next to the data) by =lookup(cell, index ranges, labels).

So I was thinking I could say after the = the cell which satisfies the lookup.Interior.Color (since I added the background to the "labels").

This is not working.

Here's what I tried:

.ForeColor.RGB = Range(Lookup(ActiveCell.Offset(0, 1).Value, Range("G28:G38"), Range("H28:H38")).Interior.Color

Thanks for any directions you can point me in

 

[jim may]

Have you tried recording a macro?

 

[largeselection]

Yes I have. However, this just tells me how to input the lookup formula into a cell on the worksheet. If I try to put this into the shape fill portion, it does not work.

As this:

With Selection.ShapeRange.Fill
        .Visible = msoTrue
        .ForeColor.RGB =FormulaR1C1 = "=LOOKUP(RC[-1],R28C7:R38C7,R28C8:R38C8)".interior.color

 

[largeselection]

Also, the answer to that formula is the value of the Bin that is in the cell. So the solution to that formula is "MID".

So that is likely why it isn't working because the formula answer is not the address of the cell.

So how can I indicate the address?