+ Reply to Thread
Results 1 to 12 of 12

Color cells in a row based on unique text that matches text on a different worksheet

Hybrid View

rn_ Color cells in a row based on... 03-07-2016, 02:07 PM
rn_ Re: Color cells in a row... 04-09-2016, 11:01 AM
PCI Re: Color cells in a row... 04-09-2016, 11:11 AM
rn_ Re: Color cells in a row... 04-09-2016, 11:25 AM
AliGW Re: Color cells in a row... 04-09-2016, 11:26 AM
rn_ Re: Color cells in a row... 04-09-2016, 11:28 AM
PCI Re: Color cells in a row... 04-09-2016, 01:41 PM
PCI Re: Color cells in a row... 04-09-2016, 02:01 PM
rn_ Re: Color cells in a row... 04-10-2016, 06:18 PM
xladept Re: Color cells in a row... 04-10-2016, 06:31 PM
PCI Re: Color cells in a row... 04-11-2016, 01:43 AM
rn_ Re: Color cells in a row... 04-11-2016, 10:46 AM
  1. #1
    Registered User
    Join Date
    06-29-2015
    Location
    CT, USA
    MS-Off Ver
    Standard 2010
    Posts
    30

    Color cells in a row based on unique text that matches text on a different worksheet

    I have a list of names, which serves as a look-up table, with a fill-in color applied to the cell (i. e., "Doe, Jane A" and her fill-in cell color is some shade of green). Other names have different colors. The lookup table has names in Column A and team names in column B.

    I have other worksheets that have the name of "Doe, Jane A" in a cell. I want to color the row of data where this name appears with the fill-in green color copied from the list of names. I'd prefer to not use Conditional Formatting to accomplish this since the names and colors in the look-up table will change and I'd rather not have to apply new VBA code whenever the list of names is amended (hence my desire to have the colors copied from the look-up table).

    I was trying to accomplish changing the fill-in color in one cell using an If statement as below, but I get #NAME?. In the example shown below, cell B2 is the cell where the name appears on the worksheet and 'Team Lookup Chart' is the aforementioned lookup table. I was hoping to use the Index function because of the changeable nature of the data entered into the lookup table.

    Thanks in advance. Any help would be greatly appreciated!

    =IF(ISNUMBER(SEARCH("Doe, Jane A",B2)), (INDEX('Team Lookup Chart'!$A:$B, (A2.Interior.Color.paste), 1)))

  2. #2
    Registered User
    Join Date
    06-29-2015
    Location
    CT, USA
    MS-Off Ver
    Standard 2010
    Posts
    30

    Re: Color cells in a row based on unique text that matches text on a different worksheet

    The following code does what I want but it is very slow (for obvious reasons since it uses two loops). The code assumes TeamLookupChart has a name with color in Column A while ResourceAssignments sheet has a corresponding name in Column B. I'm looking into seeing if I can use the Scripting.Dictionary to write code that works faster. Any suggestions are welcome:

    Sub ColorRA()
        Dim WS As Worksheet
        Dim LastCell As Range
        Dim LastCellRowNumber As Long
        Dim WS2 As Worksheet
        Dim LastCell2 As Range
        Dim LastCellRowNumber2 As Long
        Dim i As Long
        Dim j As Long
         
        Application.ScreenUpdating = False
        
        Set WS = Worksheets("TeamLookupChart")
        With WS
            Set LastCell2 = .Cells(.Rows.Count, "A").End(xlUp)
            LastCellRowNumber2 = LastCell2.Row
        End With
        
        Set WS = Worksheets("ResourceAssignments")
        With WS
            Set LastCell = .Cells(.Rows.Count, "A").End(xlUp)
            LastCellRowNumber = LastCell.Row
        End With
        
    ActiveWorkbook.Sheets("ResourceAssignments").Activate
    For i = 2 To LastCellRowNumber 'Number of rows of data in ResourceAssignments
        For j = 2 To LastCellRowNumber2 'Number of rows of data in TeamLookupChart
            If Range("B" & i).Value = Worksheets("TeamLookupChart").Range("A" & j).Value Then
                ActiveWorkbook.Sheets("TeamLookupChart").Activate
                Worksheets("TeamLookupChart").Range("A" & j, Cells(LastCellRowNumber, 1)).Copy
                ActiveWorkbook.Sheets("ResourceAssignments").Activate
                Worksheets("ResourceAssignments").Range("A" & i, Cells(LastCellRowNumber, 23)).PasteSpecial Paste:=xlPasteFormats
            End If
        Next j
    Next i
        
        Application.ScreenUpdating = True
            
    End Sub

  3. #3
    Forum Expert
    Join Date
    12-24-2007
    Location
    Alsace - France
    MS-Off Ver
    MS 365 Office Suite
    Posts
    5,085

    Re: Color cells in a row based on unique text that matches text on a different worksheet

    It will be good to have a sample file to do tests
    - Battle without fear gives no glory - Just try

  4. #4
    Registered User
    Join Date
    06-29-2015
    Location
    CT, USA
    MS-Off Ver
    Standard 2010
    Posts
    30

    Re: Color cells in a row based on unique text that matches text on a different worksheet

    Sorry but I can't figure out how to upload a test file

  5. #5
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (both in England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2503 (Windows 11 Home 24H2 64-bit)
    Posts
    90,259

    Re: Color cells in a row based on unique text that matches text on a different worksheet

    rn_ - you need to go advanced when you respond and scroll down until you see Manage Attachments.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help. It's a universal courtesy.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    NB:
    as a Moderator, I never accept friendship requests.
    Forum Rules (updated August 2023): please read them here.

  6. #6
    Registered User
    Join Date
    06-29-2015
    Location
    CT, USA
    MS-Off Ver
    Standard 2010
    Posts
    30

    Re: Color cells in a row based on unique text that matches text on a different worksheet

    Thanks AliGW. I think the test file is now attached
    Attached Files Attached Files

  7. #7
    Forum Expert
    Join Date
    12-24-2007
    Location
    Alsace - France
    MS-Off Ver
    MS 365 Office Suite
    Posts
    5,085

    Re: Color cells in a row based on unique text that matches text on a different worksheet

    "I want to color the row of data"
    is it the complete row you want to color?
    The code you sent seems not corresponding to the data: is data to check in column "A"
    Last edited by PCI; 04-09-2016 at 02:02 PM.

  8. #8
    Forum Expert
    Join Date
    12-24-2007
    Location
    Alsace - France
    MS-Off Ver
    MS 365 Office Suite
    Posts
    5,085

    Re: Color cells in a row based on unique text that matches text on a different worksheet

    Is it what you need ...?
    
    Sub Treat()
    Dim WS1  As Worksheet
    Dim WS2  As Worksheet
    Dim LR  As Long
    Dim WkRg As Range
    Dim F  As Range
    Dim ObjDic   As Object
    Set ObjDic = CreateObject("Scripting.Dictionary")
    Const FR As Integer = 2          '   First  Row
    Const NbC As Integer = 23        '   Number of columns to color
    
       Application.ScreenUpdating = False
       Set WS1 = Worksheets("TeamLookupChart")
       Set WS2 = Worksheets("ResourceAssignments")
       With WS1
          LR = .Cells(Rows.Count, "A").End(3).Row
          Set WkRg = Range(.Cells(FR, "A"), .Cells(LR, "A"))
       End With
       For Each F In WkRg
          Set ObjDic.Item(F.Value) = F
       Next F
       With WS2
          LR = .Cells(Rows.Count, "A").End(3).Row
          Set WkRg = Range(.Cells(FR, "A"), .Cells(LR, "A"))
       End With
       For Each F In WkRg
          If (ObjDic.exists(F.Value)) Then
             ObjDic.Item(F.Value).Copy
             F.Resize(, 23).PasteSpecial Paste:=xlPasteFormats
          End If
       Next F
       Application.ScreenUpdating = True
    End Sub
    Attached Files Attached Files
    Last edited by PCI; 04-09-2016 at 02:18 PM. Reason: Code changed to cover 23 columns

  9. #9
    Registered User
    Join Date
    06-29-2015
    Location
    CT, USA
    MS-Off Ver
    Standard 2010
    Posts
    30

    Re: Color cells in a row based on unique text that matches text on a different worksheet

    Thanks PCI! A little tweaking and it works like a charm! How can I modify the following section of code to paste the color into the row with an offset of 1 cell to the left?
    HTML Code: 

  10. #10
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Color cells in a row based on unique text that matches text on a different worksheet

    Use 22 instead of 23
    If I've helped you, please consider adding to my reputation - just click on the liitle star at the left.

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~(Pride has no aftertaste.)

    You can't do one thing. XLAdept

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~aka Orrin

  11. #11
    Forum Expert
    Join Date
    12-24-2007
    Location
    Alsace - France
    MS-Off Ver
    MS 365 Office Suite
    Posts
    5,085

    Re: Color cells in a row based on unique text that matches text on a different worksheet

    See
       For Each F In WkRg
          If (ObjDic.exists(F.Value)) Then
             ObjDic.Item(F.Value).Copy
             F.offset(0,-1).Resize(, 23).PasteSpecial Paste:=xlPasteFormats
          End If
       Next F

  12. #12
    Registered User
    Join Date
    06-29-2015
    Location
    CT, USA
    MS-Off Ver
    Standard 2010
    Posts
    30

    Re: Color cells in a row based on unique text that matches text on a different worksheet

    PCI - Perfect! Thanks for all of the help!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Copy lines of text cells, to another area, based on color of text.
    By richard11153 in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 01-10-2016, 03:53 PM
  2. Replies: 2
    Last Post: 09-12-2015, 11:02 AM
  3. [SOLVED] change color of cells based on text in another
    By Valleyboy in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 08-23-2015, 03:51 PM
  4. [SOLVED] Change text color of selected cells based on a text value of another cell
    By JNEWBURY2280 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-09-2014, 11:26 PM
  5. [SOLVED] Can not figure out how to color cells based on the text contained in the cell
    By Kjorg in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-07-2012, 07:00 PM
  6. [SOLVED] How do I color code a worksheet based on text
    By Travis Littlechilds in forum Excel General
    Replies: 2
    Last Post: 05-31-2005, 12:05 AM
  7. change text color based on adjacent cell text color
    By matthewst in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 03-01-2005, 03:49 PM

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