excel - VBA MACRO为选定单元格右侧的一个单元格着色

Question

我有一个宏，它制定了多个单元格区域的格式规则，并且如果它包含“S”并且它可以工作，则必须将单元格涂成黄色。但我也想要包含“S”涂成黄色的单元格右侧的单元格，但右侧只有一个单元格 - 不是整行，这可能吗？我想这将发生在“WITH 语句中，但我无法继续前进

Sub Makro2()
    Range("D6:E30,G6:H30,J6:K30,M6:N30,P6:Q30").Select
    Range("P6").Activate
    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
        Formula1:="=""S"""
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 65535
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
End Sub

Answer 1

我尝试在评论中解释尝试对不连续范围的条件格式的参与。对于单元格真正由您显示的代码 处理，您可以使用下一个代码完成您需要的工作。条件格式化行为的基础是 仅格式化条件格式所属的单元格 :

Sub Makro2Bis()
    Dim rng As Range, offrng As Range
    Set rng = Range("P6"): Set offrng = rng.Offset(0, 1)
    With rng
        .FormatConditions.Delete
        .FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, Formula1:="=""S"""
        .FormatConditions(1).SetFirstPriority
        With .FormatConditions(1).Interior
            .PatternColorIndex = xlAutomatic
            .Color = 65535
            .TintAndShade = 0
        End With
        .FormatConditions(1).StopIfTrue = False
    End With
    With offrng
        .FormatConditions.Add Type:=xlExpression, Formula1:="=" & rng.Address(0, 0) & "= ""S"""
        .FormatConditions(1).Interior.Color = rng.FormatConditions(1).Interior.Color
        .FormatConditions(1).StopIfTrue = False
    End With
End Sub

请测试它并发送一些反馈。
已编辑 :
请以您要求的方式测试(更复杂的)版本为中断范围创建条件格式:右侧相邻单元格也将具有内部黄色:

Sub Makro3Bis()
 Dim rng As Range, arr, rng1 As Range, rng2 As Range, rng3 As Range
 
 Set rng = Range("D6:E30,G6:H30,J6:K30,M6:N30,P6:Q30")
 arr = buildThreeRngs(rng)
 Set rng1 = arr(0) 'the first column of the discontinuous range areas
 Set rng2 = arr(1) 'the second column of the discontinuous range areas
 Set rng3 = arr(2) 'the next column after the discontinuous range areas
 
 With rng1
    .FormatConditions.Delete
    .FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, Formula1:="=""S"""
    .FormatConditions(1).SetFirstPriority
    With .FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 65535
        .TintAndShade = 0
    End With
    .FormatConditions(1).StopIfTrue = False
 End With
 With rng2 'it will have two conditions. The second one relative to its left neighbour cell.
    .FormatConditions.Delete
    .FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, Formula1:="=""S"""
    .FormatConditions(1).SetFirstPriority
    With .FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 65535
        .TintAndShade = 0
    End With
    .FormatConditions(1).StopIfTrue = False
    .FormatConditions.Add Type:=xlExpression, Formula1:="=" & rng1.cells(1).Address(0, 0) & "= ""S"""
    .FormatConditions(2).Interior.Color = rng1.FormatConditions(1).Interior.Color
    .FormatConditions(2).StopIfTrue = False
 End With
 With rng3
    .FormatConditions.Delete
    .FormatConditions.Add Type:=xlExpression, Formula1:="=" & rng2.cells(1).Address(0, 0) & "= ""S"""
    .FormatConditions(1).Interior.Color = rng.FormatConditions(1).Interior.Color
    .FormatConditions(1).StopIfTrue = False
 End With
End Sub

请测试它并发送一些反馈。
第二次编辑 :
仅查看您的图片而不是基于您发布的代码，可能我的第一个代码(用于一个单元格)以下一种方式调整应该是您需要的:

Sub Makro2BisBis()
    Dim rng As Range, offrng As Range
    Set rng = Range("D6:D30,G6:G30,J6:J30,M6:M30,P6:P30")
    Set offrng = rng.Offset(0, 1)
    Debug.Print offrng.Address: Stop
    With rng
        .FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, Formula1:="=""S"""
        .FormatConditions(1).SetFirstPriority
        With .FormatConditions(1).Interior
            .PatternColorIndex = xlAutomatic
            .Color = 65535
            .TintAndShade = 0
        End With
        .FormatConditions(1).StopIfTrue = False
    End With
    With offrng
        .FormatConditions.Add Type:=xlExpression, Formula1:="=" & rng.cells(1).Address(0, 0) & "= ""S"""
        .FormatConditions(1).Interior.Color = rng.FormatConditions(1).Interior.Color
        .FormatConditions(1).StopIfTrue = False
    End With
End Sub

它将使您在代码中的范围内的单元格变为黄色，但仅使用每个区域的第一列...
请测试它并发送一些反馈。