Forum Discussion

ManchesterBlue22's avatar
ManchesterBlue22
Copper Contributor
Nov 08, 2024

Need help with this work project pleaseeeee

Hello,

I have a spreadsheet with all my data in rows. Within the rows I have conditional formatting to turn a cell red if there is an error, along with a formula that adds "-NO MATCH" if there is an error which is working fine. However I'm trying to create a column with a formula that will result in "YES" or "NO" if the formula detects a red highlight OR "-NO MATCH" within the row. I've been working on this for what feels like hours and can't solve it, I've tried many different ways. One thing to note, every cell has a formula within it, which I feel may be the problem? I need the cells to stay as a formula and not text. 

I've tried the following:

=IF(B3:HB3="-NO MATCH","YES',"NO")

=IF(B3:HB3="FALSE*","YES")

=IF(B3:HB3=FALSE,"YES")

I can't seem to create a formula that can work within a range of B3:HB3 and each cell having a formula..... PLS HELP!

excel
Formulas and Functions
Macros and VBA
  • Mks_1973's avatar
    Mks_1973
    Iron Contributor
    Nov 08, 2024

    Detect "-NO MATCH" Text in the Range
    We can use the COUNTIF function to check if any cell within the range B3:HB3 contains "-NO MATCH". If any cell contains this, COUNTIF will return a number greater than 0, which we can then use in an IF statement.

    Place this formula in the cell where you want the YES or NO result:

    =IF(COUNTIF(B3:HB3, "*-NO MATCH*") > 0, "YES", "NO")

    COUNTIF(B3:HB3, "*-NO MATCH*"): This part checks each cell in the range B3:HB3 to see if it contains "-NO MATCH". The asterisks (*) are wildcard characters that allow for partial matches, so the formula works even if "-NO MATCH" is part of a larger string.
    > 0: If any cell in the range contains "-NO MATCH", COUNTIF will return a number greater than 0.
    IF(..., "YES", "NO"): If the count is greater than 0, this formula will return "YES". If no cells contain "-NO MATCH", it will return "NO".


    This formula only detects "-NO MATCH" and cannot detect cell color. Excel functions cannot directly check for conditional formatting or cell color.

    If you need to detect cell color, it would require VBA (macros) because regular Excel functions don’t have access to cell formatting properties like color.

     

     

    VBA macro that checks each cell in the specified range (B3:HB3) for either the "-NO MATCH" text or a red fill color (based on your conditional formatting). If it finds any cell that meets these conditions, it will set the result cell to "YES". Otherwise, it will set it to "NO"

    Function CheckForError(rng As Range) As String
        Dim cell As Range
        Dim hasError As Boolean
        hasError = False

        ' Loop through each cell in the range
        For Each cell In rng
            ' Check if the cell contains "-NO MATCH"
            If InStr(1, cell.Value, "-NO MATCH", vbTextCompare) > 0 Then
                hasError = True
                Exit For
            End If

            ' Check if the cell's fill color is red
            If cell.Interior.Color = RGB(255, 0, 0) Then
                hasError = True
                Exit For
            End If
        Next cell

        ' Return "YES" if there's an error, "NO" otherwise
        If hasError Then
            CheckForError = "YES"
        Else
            CheckForError = "NO"
        End If
    End Function

    In the cell where you want the result (YES or NO), enter this formula:
    =CheckForError(B3:HB3)

Resources