=INDEX('Sheet4'!$A$1:$J$170, MATCH(B2,'Sheet4'!$A$1:$A$170,),
MATCH(B1,'Sheet4'!$A$1:$J$1,))

I am using the above formula on another sheet that when you type in a
variable to lookup on sheet 4, it grabs the data and puts into the cell on
sheet 1.. This works great.. BUT I would like to keep the text color from the
data on sheet 4 so that when it does its lookup and inputs the data into the
cell on sheet 1, it keeps the text color from the data on sheet 4.. Any ideas?

You might try a change event.
This will fire when you enter the variable.
Trap the cell address of the formula cell and the transferred cell
trap the font color of the transferred cell and apply it to the formula
cell.

let us know if you need more help...
--
steveB

Remove "AYN" from email to respond
"Woody" wrote in message
...
=INDEX('Sheet4'!$A$1:$J$170, MATCH(B2,'Sheet4'!$A$1:$A$170,),
MATCH(B1,'Sheet4'!$A$1:$J$1,))

I am using the above formula on another sheet that when you type in a
variable to lookup on sheet 4, it grabs the data and puts into the cell on
sheet 1.. This works great.. BUT I would like to keep the text color from
the
data on sheet 4 so that when it does its lookup and inputs the data into
the
cell on sheet 1, it keeps the text color from the data on sheet 4.. Any
ideas?

Not sure how to do that, I was lucky to get it to work at all. I am pretty
new to working with Excel this way.

What I am doing is on sheet 1 entering in a date and a serial number, the
formula looks it up on sheet 4 using the MATCH formula. I have changed the
test color on Sheet 4 on some of the entries to setup a color code scheme.
When I enter the data on sheet 1 I would like it to return the data from
sheet 4 with the color of text it was in. I hope that makes more sense.

"STEVE BELL" wrote:

You might try a change event.
This will fire when you enter the variable.
Trap the cell address of the formula cell and the transferred cell
trap the font color of the transferred cell and apply it to the formula
cell.

let us know if you need more help...
--
steveB

Remove "AYN" from email to respond
"Woody" wrote in message
...
=INDEX('Sheet4'!$A$1:$J$170, MATCH(B2,'Sheet4'!$A$1:$A$170,),
MATCH(B1,'Sheet4'!$A$1:$J$1,))

I am using the above formula on another sheet that when you type in a
variable to lookup on sheet 4, it grabs the data and puts into the cell on
sheet 1.. This works great.. BUT I would like to keep the text color from
the
data on sheet 4 so that when it does its lookup and inputs the data into
the
cell on sheet 1, it keeps the text color from the data on sheet 4.. Any
ideas?

Steve was suggesting you try something like this: Right click on the sheet
tab for Sheet1 and select View Code. Then paste the following code onto the
code module for the sheet:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim TCol As Integer
Dim TRow As Long
Dim Colour As Integer
If Target.Address = "$B$1" Or Target.Address = "$B$2" Then
TCol = Application.WorksheetFunction.Match(Range("B1"), _
Sheets("Sheet4").Range("A1:J1"), 1)
TRow = Application.WorksheetFunction.Match(Range("B2"), _
Sheets("Sheet4").Range("A1:A170"), 1)
Colour = Sheets("Sheet4").Cells(TRow, TCol).Font.ColorIndex
Range("B3").Font.ColorIndex = Colour
End If
End Sub

This will copy the relevant font color from Sheet4 to cell B3 on sheet1.

Regards
Rowan

"Woody" wrote:

Not sure how to do that, I was lucky to get it to work at all. I am pretty
new to working with Excel this way.

What I am doing is on sheet 1 entering in a date and a serial number, the
formula looks it up on sheet 4 using the MATCH formula. I have changed the
test color on Sheet 4 on some of the entries to setup a color code scheme.
When I enter the data on sheet 1 I would like it to return the data from
sheet 4 with the color of text it was in. I hope that makes more sense.

"STEVE BELL" wrote:

You might try a change event.
This will fire when you enter the variable.
Trap the cell address of the formula cell and the transferred cell
trap the font color of the transferred cell and apply it to the formula
cell.

let us know if you need more help...
--
steveB

Remove "AYN" from email to respond
"Woody" wrote in message
...
=INDEX('Sheet4'!$A$1:$J$170, MATCH(B2,'Sheet4'!$A$1:$A$170,),
MATCH(B1,'Sheet4'!$A$1:$J$1,))

I am using the above formula on another sheet that when you type in a
variable to lookup on sheet 4, it grabs the data and puts into the cell on
sheet 1.. This works great.. BUT I would like to keep the text color from
the
data on sheet 4 so that when it does its lookup and inputs the data into
the
cell on sheet 1, it keeps the text color from the data on sheet 4.. Any
ideas?

Woody,

To add to Rowan's reply -

Here are 2 worksheet change event macros (choose one).
(be sure to put it into the Sheet1 module)

They both compare sheets 1 & 2 (change to your needs)
I used columns A (1) and B (2) as the data columns (change to your needs.

The first just copies sheet2 to sheet 1.
The second sets the value & color on sheet 1.
Each works on the 2nd column dependent on the value in column 1.

Index(Match) function returns the value from column 2 on sheet2.

But use caution: The match function will error out if the value is not
found.

I usually get around this by doing an If statement before the transfer...
If Worksheetfunction.Countif(Sheets("Sheet2").Columns (1),target)0 then
' do your stuff

else MsgBox "No Data Found"

end if
==========================================
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rw As Long, clr

Applicatiion.EnableEvents = False

If Target.Column = 1 Then
rw = WorksheetFunction.Match(Target, Sheets("Sheet2").Columns(1), 0)
Target.Offset(0, 1) = Sheets("Sheet2").Cells(rw, 2)
Sheets("Sheet2").Cells(rw, 2).Copy _
Destination:=Target.Offset(0, 1)
End If

Applicatiion.EnableEvents = False

End Sub
==========================================
Private Sub Worksheet_Change(ByVal Target As Range)

Dim rw As Long, clr

Applicatiion.EnableEvents = False

If Target.Column = 1 Then
rw = WorksheetFunction.Match(Target, Sheets("Sheet2").Columns(1), 0)
clr = Sheets("Sheet2").Cells(rw, 2).Font.Color
Target.Offset(0, 1) =
WorksheetFunction.Index(Sheets("Sheet2").Columns(2 ), rw, 1)
Target.Offset(0, 1).Font.Color = clr
End If

Applicatiion.EnableEvents = True

End Sub
==========================================
--
steveB

Remove "AYN" from email to respond
"Woody" wrote in message
...
Not sure how to do that, I was lucky to get it to work at all. I am pretty
new to working with Excel this way.

What I am doing is on sheet 1 entering in a date and a serial number, the
formula looks it up on sheet 4 using the MATCH formula. I have changed the
test color on Sheet 4 on some of the entries to setup a color code scheme.
When I enter the data on sheet 1 I would like it to return the data from
sheet 4 with the color of text it was in. I hope that makes more sense.

"STEVE BELL" wrote:

You might try a change event.
This will fire when you enter the variable.
Trap the cell address of the formula cell and the transferred cell
trap the font color of the transferred cell and apply it to the formula
cell.

let us know if you need more help...
--
steveB

Remove "AYN" from email to respond
"Woody" wrote in message
...
=INDEX('Sheet4'!$A$1:$J$170, MATCH(B2,'Sheet4'!$A$1:$A$170,),
MATCH(B1,'Sheet4'!$A$1:$J$1,))

I am using the above formula on another sheet that when you type in a
variable to lookup on sheet 4, it grabs the data and puts into the cell
on
sheet 1.. This works great.. BUT I would like to keep the text color
from
the
data on sheet 4 so that when it does its lookup and inputs the data
into
the
cell on sheet 1, it keeps the text color from the data on sheet 4.. Any
ideas?