Find ColorIndex

R

rickey24

Hi all

I am trying to use the value in Cell D2 on sheet sht, then find th
cell where that value is on sheet sTL, take the interior colorindex o
that cell and apply it to some formatting on a sheet that I am adding.

Dim Cind as Long
Dim LookRng as Range, FndRng as Range
Dim Stl as Worksheet 'sheet 1
Dim sht as worksheet 'sheet 2

Set LookRng = sTL.Range("b4", sTL.Range("b"
sTL.Rows.Count).End(xlUp))
Set FndRng = LookRng.Find(sht.Range("D2").Value)
Cind = FndRng.Offset(0, 1).Interior.ColorIndex
...
Selection.Interior.ColorIndex = cind

I may be going about this all the wrong way, but I am getting an erro
(91) on the cind = line.

Any help would be greatly appreciated. Thanks.
R
 
A

Andoni

Try this!

Sub AAA()
Dim Cell_Value As Variant
Dim Cell_ColoIndex As Byte

Cell_Value = Sheets("Sht").Range("D2").Value
On Error Resume Next
With Sheets("sTL")
.Visible = True
.Activate
.Range("A1").Select
Cells.Find( _
What:=Cell_Value, _
After:=ActiveCell, _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False).Activate
End With
Cell_ColoIndex = ActiveCell.Interior.ColorIndex
'then apply this color index to you new sheet
End Su
 
R

rickey24

Hi
I am getting an error 91 on the Cells.Find equation, can anybody els
possibly help me out? Thanks.
R
 
Top