Airman
Lv 4

# VBA~請修正公式語法錯誤之處。謝謝!!!

T7=IF(OR(T\$6="",\$S7="",\$S7>=T\$6,T\$3<=0,\$S7+T\$3-X<=0,\$S7-X<=0),"",IF(OR(\$S\$5=OFFSET(\$I\$6,\$S7+T\$3,1,,7))*OR(SUM((OFFSET(\$I\$6,\$S7,1,,7)=\$S\$5)*OFFSET(\$A\$7,\$S7-X,1,,7))=OFFSET(\$A\$7,\$S7+T\$3-X,1,,7)),\$S7+T\$3,""))

1. =\$S7的 I 欄期數之J︰P=\$S\$5數值的儲存格標示淺藍色。

2.上述=\$S\$5的儲存格(含)向左=\$S7-X之 A 欄期數的B︰H之對應數值儲存格標示淺藍色。

3.上述之對應數值亦有顯示在=\$S7+T\$3-X的 A 欄期數之B︰H的儲存格，亦標示淺藍色。

Rating
• 顯栓
Lv 7

Dim Rx, Ry

列127

Ry = Sheets(1).Range("S" & b.Row) * 2 - Sheets(1).[T6] + 7 '\$S7-X

Sheets(1).Range("J" & Ry & ":" & "P" & Ry).Select

Selection.FormatConditions.Delete '設定格式化條件清除

'公式為:

Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=J" & Ry & "=\$S\$5"

Selection.FormatConditions(1).Interior.ColorIndex = 8 '設定格式化條件 (圖樣顏色)

Sheets(1).Range("B" & Ry & ":" & "H" & Ry).Select

Selection.FormatConditions.Delete

Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=J" & Ry & "=\$S\$5"

Selection.FormatConditions(1).Interior.ColorIndex = 8

Rx = Sheets(1).Range("S" & b.Row) * 2 + Sheets(1).[T3] - Sheets(1).[T6] + 7 '\$S7+T\$3-X

Sheets(1).Range("J" & Rx & ":" & "P" & Rx).Select

Selection.FormatConditions.Delete

Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=J" & Rx & "=\$S\$5"

Selection.FormatConditions(1).Interior.ColorIndex = 27

Sheets(1).Range("B" & Rx & ":" & "H" & Rx).Select

Selection.FormatConditions.Delete

"=COUNTIF(B" & Rx & ",SUMIF(\$J" & Ry & ":\$P" & Ry & ",\$S\$5,\$B" & Ry & ":\$H" & Ry & "))"

Selection.FormatConditions(1).Interior.ColorIndex = 8

請修改測試!