Conditional Formatting on a MATCH/LOOKUP function

[Davidwazza]

Hi all,

I have an "actual build" worksheet, which allows the user to select which materials were used, material properties (e.g. material grade etc.), including its corresponding length. The user can enter all these in manually, or select the material name from a pre-defined list (Reference worksheet contains this information), and then the associated properties are automatic lookups from the Reference worksheet.

So all this is fine / completed, and the LOOKUP returns its theoretical length from the reference table. However, its actual material lengths will almost never match the theoretical length, so what I want to happen is for a conditional format to highlight the cell, to notify the user that they need to enter a manual value. The auto-looked up length is still useful a proposed build on the worksheet, but having it highlighted to ensure it is manually entered later is the objective.

The other thing is it can't just check if it's a formula, it has to check if it is a formula with a MATCH/LOOKUP function. E.g. it needs to be unhighlighted if someone has a basic formula, ie = 2*0.42 if there two of the item etc..

Hence, I was thinking of having a conditional formula that "format only cells that contain" and then format only cells with "specific text" and "containing" and "MATCH(" text function, however this doesn't seem to work. So any ideas of how to get it what I want?

Length cell formula:
=OFFSET('Reference- Materials'!$N$5, MATCH($BO32, Materials_Item_Number, 0), 0) * $AH32

Thanks! David.

 

[pbornemeier]

I couldn't get this UDF to work in a conditional formatting formula, but if it is placed in an adjacent cell, it would return TRUE if the referenced cell contains MATCH and conditional formatting could be applied to that cell.

Function FormulaContainsMatch(sWorksheet, sRange)
    Application.Volatile
    FormulaContainsMatch = False
    If InStr(Worksheets(sWorksheet).Range(sRange).Formula, "MATCH") Then
        FormulaContainsMatch = True
    End If
End Function

Another possibility:
I couldn't get this to work as you desired, but it may help:

If you reference this formula in a named range:
=GET.CELL(6,Sheet1!$A1)
referencing the named range will return the text of the formula in Sheet1!A1

 

[Davidwazza]

ok thanks Phil, I pretty much used your idea to display the formula in another column, and then had my conditional formatting check if there is a MATCH function within this formula, cheers again.

Function GetFormula(Cell As Range) As String
    Application.Volatile
    GetFormula = Cell.Formula
End Function