时间戳

首页 > 解决方案 > VBA conditional search and marking

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

标签： searchconditionalcellbackground-color

解决方案

编辑：见下文

有点不确定你在问什么，但我认为解决方案如下。我使用了更简单的代码，但应该足够了。

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

推荐阅读