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

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

You might find some useful code for the array at
http://www.xldynamic.com/source/xld.ColourCounter.html

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"David Adamson" wrote in message
...
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

Thanks for that Bob. I'll have a good look and see what i can do.

The other thing I can do which would be very simple is just simply use a
Select case statement for the colourindex its just not as pritty though


"Bob Phillips" wrote in message
...
You might find some useful code for the array at
http://www.xldynamic.com/source/xld.ColourCounter.html

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

"David Adamson" wrote in message
...
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

Bob,

Thanks I ended up going the select case as I'm just lazy.

regards

David

Sub Dodgy()
Dim FindP As Double
Dim FindW As Double
Dim location As Range
Dim res As Double
Dim PGrowth, WGrowth As Double
Dim r, c, i As Integer

i = 0
For r = 0 To 3 ' 4 row
For c = 0 To 3 ' 4 columns

With Worksheets("trial")
'get colour of cells
FindP = .Cells(4, 2).Offset(r, c).Interior.ColorIndex
FindW = .Cells(10, 2).Offset(r, c).Interior.ColorIndex
'find value of colour (pasture and weed)
PGrowth = PastureVal(FindP)
WGrowth = WeedVal(FindW)
'paste back data where required
.Cells(16, 2 + i) = PGrowth
.Cells(17, 2 + i) = WGrowth
'make sure that cells moves
i = i + 1
End With

Next c
Next r

End Sub

Function PastureVal(Find As Double)
Dim val(6) As Double
Dim PVal(6) As Double

Dim k As Integer
Dim res As Double
'set data sets (color and value of pasture growth)
With Worksheets("trial")
For k = 0 To 6
val(k) = .Range("r3").Offset(k, 0).Interior.ColorIndex
PVal(k) = .Range("s3").Offset(k, 0)
Next k
End With
'find correct colour and value
Select Case Find

Case val(0)
res = PVal(0)

Case val(1)
res = PVal(1)

Case val(2)
res = PVal(2)

Case val(3)
res = PVal(3)

Case val(4)
res = PVal(4)

Case val(5)
res = PVal(5)

Case val(6)
res = PVal(6)
End Select

PastureVal = res

End Function

Function WeedVal(Find As Double)
'same as above
End Function