one way to do it is to compare the ColorIndex of the cells in your VBA code.
Something like this:


Dim res As Double
Dim c As Range

With Worksheets("trial")
For Each c In .Range("R3:R12")
If c.Interior.ColorIndex = .Range("B4").Interior.ColorIndex Then
res = c.Offset(0, 1).Value
Exit For
End If
Next c
End With


"David Adamson" wrote:

I need to do a vlookup based on the colorindex property of a cell. Is it
possible?

What I have is a series of cells that change colour and the value needs to
update


Dim find As Double
Dim location As Range
Dim res As Double

With Worksheets("trial")
find = .Range("b4").Interior.ColorIndex
Set location = .Range("s3:r12")
End With

res = Application.WorksheetFunction.VLookup(find, location, 2, 0)

The other thing I was also attempting was to populate an array or list with
the colorindex. Then if possible use listindex and use offset to get the
value I needed but my brain is fried and any suggestions would be
appreciated