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?