Forum Discussion

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
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:

ColorCell: a cell with the fill color to search for.

LookupRange: the range with colored cells.

ResultRange: the range with values to return. This must be the same size as the lookup range.

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.
ColorLookup.xlsm16 KB

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

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:

ColorCell: a cell with the fill color to search for.
LookupRange: the range with colored cells.
ResultRange: the range with values to return. This must be the same size as the lookup range.
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.

ColorLookup.xlsm16 KB

jurkasovak
Copper Contributor
Feb 01, 2023
HansVogelaar

Hi Hans!

I have an example: Jane should be red
- HansVogelaar
  MVP
  Feb 01, 2023
  jurkasovak
  
  I don't see how that would work. What exactly do we have to look up and where?
  - jurkasovak
    Copper Contributor
    May 16, 2023
    HansVogelaar
    
    Hi Hans!
    
    I don´t want find the colour.
    I need find a text with function xlookup and result must contain cell background color
    
    I stilll doing myself by hand and it is time consuming.
    
    Katka.

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

statusByColor.xlsm21 KB