Thread: Return a cell value as well as its background color

Hello all,

I have a table with the columns: id, company and year, eg:
2 Dell 1996
2 Microsoft 1999
5 Apple 1997
5 Oracle 1999
5 Amazon 2000

I want to look in this table for a certain id and a certain year (some ids show up multiple times with multiple companies and years, I want hte first instance of an id and year match), and then print the company in the appropriate cell in another 2d table where the left column is just IDs and the row at the top is years.

1995 1996 1997 1998 1999 2000
2 Dell Microsoft
5 Apple Oracle Amazon
I have done this successfully using the following function, but now I also want it to return the color of the cell, not just its value.
---

Function PrintCompany(ID As Long, Year As Integer, cells As Range)
  Dim Pos As Range, count As Integer
  count = 1
  Set Pos = cells(1, 1)
  Do While (count <= cells.Rows.count)
  PrintCompany = ""    
      If (ID = Pos.Value And Pos.Offset(0, 2).Value = Year) Then
        PrintCompany = Pos.Offset(0, 1).Value
        'ActiveCell.Interior.ColorIndex = pos.Offet(0, 1).Interior.ColorIndex <- not working
        'currentcell.Interior.Color = pos.Offset(0, 1).Interior.Color <- not working
       Exit Do
       Else: PrintCompany = ""
     End If
     count = count + 1
     Set Pos = cells(count, 1)
  
    Loop
    
  End Function

--------

I have tried calling a sub from the function to do just that but I don't know how to do it right. I have tried all sorts of other things to no avail. Any help would be appreciated.

This is just a guess at what you are trying to do.

A sample workbook would not go amiss
It should clearly illustrate your problem and not contain any sensitive data.

Select a company name in column B to see the result

Thanks for your response!

Yes, this is almost what I am trying to do, except when I convert from the first table to the second I want the color to permanently be transferred / highlighted along with it. Right now, in your file, when I select a company name in column 2, the background color appears in the second table briefly/till I select something else.

I have attached a test file to show how my data was, where I am, and my goal. Since my code already works to place each company in the right cell, any find and match and copy format help to compare Table2 with List1 would work. I have over 2000 distinct IDs so I cannot do this manually.

Thank you again!

Hello ysouljah,

If you are coloring the cells using Conditional Formatting then VBA will not be able to return the cell color. Conditional Formatting does not affect the cell's Interior property.

This might be a possible solution.

Select from Column B

Somehow I get the feeling that there is a little more to this than you have so far declared.

Hello ysouljah,

Thanks for update. I mentioned the Conditional Formatting piece because I have written code to change cell colors for people and they said it didn't work. The reason turned out to be they were using Conditional Formatting. It is one of those programming "gems" you find once in a while.

Try the code in the Workbook module then in the Sheet Selection Change event
Option Explicit

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    Dim RowNo As Long, ColNo As Long
    Dim LastRow As Long
    Dim ColourIndex As Long
    Dim isect As Range

    If Sh.Name = "Sheet3" Then Exit Sub
    If Target.Cells.Count > 1 Then Exit Sub

    LastRow = Range("A" & Rows.Count).End(xlUp).Row

    Set isect = Intersect(Target, Range("B2:B" & LastRow))

    If Not isect Is Nothing Then
        RowNo = Range("F:F").Find(Target.Offset(0, -1)).Row
        ColNo = Range("1:1").Find(Target.Offset(0, 1)).Column
        Cells(RowNo, ColNo) = Target
        Cells(RowNo, ColNo).Interior.ColorIndex = Range("O:O").Find(Target).Interior.ColorIndex
    End If

End Sub

This should do as you need in all the sheets in your workbook, provided they all have exactly the same layout.

I have added this line

If Sh.Name = "Sheet3" Then Exit Sub

This means the code will fire in all sheets except "Sheet3", take this line out if it is not required.
I put it in only as an example to illustrate that you can use that feature to do different things with different groups of sheets. For instance you could use a Select Case sh.Name statement to apply code to different groups of sheets.

If each sheet is different and needs individually coded then use the Worksheet Modules for each sheet.
If groups or all sheets use the same code then use the Workbook Modules.

There is no need to change the variable names.

Hope this helps