Forum Discussion

jurkasovak's avatar
jurkasovak
Copper Contributor
Jan 27, 2023
Solved

xlookup with format

Hi!   How I can use function xlookup with format. For example If I want find cell with color...   Thanks.
excel
  • HansVogelaar's avatar
    HansVogelaar
    Jan 30, 2023

    jurkasovak 

    Here is a simplistic function.

    Function XLookupColor(ColorCell As Range, LookupRange As Range, _
        ResultRange As Range, IfNotFound As Variant) As Variant
    Dim LookupColor As Long
    Dim i As Long
    Application.Volatile
    LookupColor = ColorCell.Interior.Color
    For i = 1 To LookupRange.Count
        If LookupRange(i).Interior.Color = LookupColor Then
            XLookupColor = ResultRange(i).Value
            Exit Function
        End If
    Next i
    XLookupColor = IfNotFound
End Function

    The function has four arguments:

    1. ColorCell: a cell with the fill color to search for.
    2. LookupRange: the range with colored cells.
    3. ResultRange: the range with values to return. This must be the same size as the lookup range.
    4. IfNotFound: the value to return if the color is not found. Use "" if you want to return a blank.

    Warning: the result will not be updated automatically if you change the color of the color cell or the color range. Press F9 to force a recalculation.

    Sample workbook attached. You'll have to allow macros when you open it.

HansVogelaar's avatar
HansVogelaar
MVP
Jan 27, 2023

jurkasovak 

Excel's lookup functions can only search for a cell's value, not its formatting.

If the lookup range has been colored using conditional formatting, you should use the conditions of the conditional formatting rules, Otherwise, you'd need VBA code.

jurkasovak's avatar
jurkasovak
Copper Contributor
Jan 30, 2023
Hi Hans!

Thank you for answer. Unfortunately, I cannot use conditional formatting, because the source file does not have data that is colored according to the rule (my colleague marks the status of the order according to her own color).
Do you have VBA code?

Thanks, Katka.
  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Jan 30, 2023

    jurkasovak 

    Here is a simplistic function.

    Function XLookupColor(ColorCell As Range, LookupRange As Range, _
        ResultRange As Range, IfNotFound As Variant) As Variant
    Dim LookupColor As Long
    Dim i As Long
    Application.Volatile
    LookupColor = ColorCell.Interior.Color
    For i = 1 To LookupRange.Count
        If LookupRange(i).Interior.Color = LookupColor Then
            XLookupColor = ResultRange(i).Value
            Exit Function
        End If
    Next i
    XLookupColor = IfNotFound
End Function

    The function has four arguments:

    1. ColorCell: a cell with the fill color to search for.
    2. LookupRange: the range with colored cells.
    3. ResultRange: the range with values to return. This must be the same size as the lookup range.
    4. IfNotFound: the value to return if the color is not found. Use "" if you want to return a blank.

    Warning: the result will not be updated automatically if you change the color of the color cell or the color range. Press F9 to force a recalculation.

    Sample workbook attached. You'll have to allow macros when you open it.

    • jurkasovak's avatar
      jurkasovak
      Copper Contributor
      Feb 01, 2023

      HansVogelaar 

       

      Hi Hans!

       

      I have an example: Jane should be red

       

       

      • HansVogelaar's avatar
        HansVogelaar
        MVP
        Feb 01, 2023

        jurkasovak 

        I don't see how that would work. What exactly do we have to look up and where?

    • PeterBartholomew1's avatar
      PeterBartholomew1
      Silver Contributor
      Jan 30, 2023

      HansVogelaar 

      Your VBA programming is better than mine.  I have been so locked into Lambda that I have largely forgotten VBA!  My attempt was

      Sub WriteStatus()
Dim rng As Range, c As Range
Dim state As String
Dim color

Set rng = Range("coloredRange")
    For Each c In rng
        color = c.Interior.ColorIndex
        Select Case color
            Case 43
                state = "paid"
            Case 44
                state = "urgent"
            Case 3
                state = "overdue"
            Case 6
                state = "due"
            Case 33
                state = "in process"
            Case Else
                state = "not found"
        End Select
        c.Value = state
    Next
    
End Sub

       

Resources