Search code examples
searchconditional-statementscellbackground-color

VBA conditional search and marking

The aim is to search in column A for the word "XXX". When the word is found in that column, the next cell should match the word "C".If it doesn't, carry on with the search for "XXX" in the next row, etc. The issue in my code is that it marks all "C"s. In other words, there is no conditional search where XXX=C.

enter image description here

For Each cell In ws.Range("A1:A20").SpecialCells(xlCellTypeConstants)
    Select Case cell.Value2
        Case "XXX"
            col = RGB(202, 225, 255)
        Case Else
            col = 0
    End Select


    If col > 0 Then
        wb.Activate
        cell.Interior.Color = col
        For Each cell2 In cell.Offset(, 2).Resize(1).SpecialCells  (xlCellTypeConstants)
        res = Switch(cell2.Value = "C", vbGreen)
        If Not IsNull(res) Then Intersect(Range("B:B, J:J, L:L, N:N, Q:Q"), Rows(cell2.Row)).Interior.Color = CLng(res) '
        Next
    End If
Next
Solution

  • Edited: See Below

    Slightly unsure what you're asking but I think solution is below. I have used much simpler code but it should be adequate.

    For xJ = 1 to 200
    If Range("A" & xJ).Value = "XXX" then
        Range("A" & xJ).Interior.Color = RGB(202, 225, 255)
        If Range("B" & xJ).Value = "C" Then
            Range("B" & xJ).Interior.Color = RGB(57, 225, 20)
        End If
    End if
Next xJ