+ Reply to Thread
Results 1 to 5 of 5

Keep text color from a Index Match Formula

  1. #1
    Woody
    Guest

    Keep text color from a Index Match Formula

    =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?

  2. #2
    STEVE BELL
    Guest

    Re: Keep text color from a Index Match Formula

    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" <Woody@discussions.microsoft.com> wrote in message
    news:1F330CC5-D6E3-43AB-B84D-C7F2E0CB63E8@microsoft.com...
    > =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?




  3. #3
    Woody
    Guest

    Re: Keep text color from a Index Match Formula

    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" <Woody@discussions.microsoft.com> wrote in message
    > news:1F330CC5-D6E3-43AB-B84D-C7F2E0CB63E8@microsoft.com...
    > > =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?

    >
    >
    >


  4. #4
    Rowan
    Guest

    Re: Keep text color from a Index Match Formula

    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" <Woody@discussions.microsoft.com> wrote in message
    > > news:1F330CC5-D6E3-43AB-B84D-C7F2E0CB63E8@microsoft.com...
    > > > =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?

    > >
    > >
    > >


  5. #5
    STEVE BELL
    Guest

    Re: Keep text color from a Index Match Formula

    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" <Woody@discussions.microsoft.com> wrote in message
    news:4DBC51B9-483F-42CD-8ED4-3FBF2907DAAD@microsoft.com...
    > 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" <Woody@discussions.microsoft.com> wrote in message
    >> news:1F330CC5-D6E3-43AB-B84D-C7F2E0CB63E8@microsoft.com...
    >> > =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?

    >>
    >>
    >>




+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1