【求助】for each和with嵌套出错（无法多表选中相同区域）

舞文弄墨123

gufengaoyue

试试 with 前面加一句：ws.activate  先激活该工作表。

abc123281

有隐藏的工作表
Sub 执行()

Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
  If IsNumeric(Trim(ws.Name)) Then
    ws.Activate
    With ws.Range("D6:E37")
   .Select
   .FormatConditions.Delete
   .FormatConditions.Add Type:=xlExpression, Operator:=-1, Formula1:="=XLOOKUP(D6,'1.楼栋明细表'!$F:$F,'1.楼栋明细表'!$P:$P)='1'!$J$2", Formula2:=""
   .FormatConditions(1).Interior.Color = [K2].Value
   End With
  
    With ws.Range("D7:E37")
   .Select
   .FormatConditions.Add Type:=xlExpression, Operator:=-1, Formula1:="=XLOOKUP(D6,'1.楼栋明细表'!$F:$F,'1.楼栋明细表'!$P:$P)='1'!$J$2", Formula2:=""
   .FormatConditions(2).Interior.Color = [K2].Value
   End With
  
  
   With ws.Range("G6:H37")
   .Select
   .FormatConditions.Delete
   .FormatConditions.Add Type:=xlExpression, Operator:=-1, Formula1:="=XLOOKUP(D6,'1.楼栋明细表'!$F:$F,'1.楼栋明细表'!$P:$P)='1'!$J$2", Formula2:=""
   .FormatConditions(1).Interior.Color = [K2].Value
   End With
  
    With ws.Range("G7:H37")
   .Select
   .FormatConditions.Add Type:=xlExpression, Operator:=-1, Formula1:="=XLOOKUP(D6,'1.楼栋明细表'!$F:$F,'1.楼栋明细表'!$P:$P)='1'!$J$2", Formula2:=""
   .FormatConditions(2).Interior.Color = [K2].Value
   End With
End If
Next ws

End Sub

舞文弄墨123

gufengaoyue 发表于 2024-7-9 14:32
试试 with 前面加一句：ws.activate  先激活该工作表。

对的，太感谢了，高手啊

舞文弄墨123

abc123281 发表于 2024-7-9 15:24
有隐藏的工作表
Sub 执行()

感谢帮助，太强大了