Excel VBA 同列變色

excel vba

longtimetalk 2021-02-15 00:45:25 ‧ 1252 瀏覽

分享至

依照慣例，先在此感謝各位閱讀與幫忙

以下為條件：
1.A到F欄位，同列有兩個以上 0時變色

以下列出自己撰寫A到C三個欄位內容，個人感覺太冗長，所以求教更簡便的寫法。

Sub test()
For i = 1 To 20
    For k = 1 To Range("A1").End(xlToRight).Column
            If Cells(i, 1) <> 0 Then
                ElseIf Cells(i, 1) = Cells(i, 2) Then
                Cells(i, k).Interior.Color = RGB(255, 255, 0)
            End If

            If Cells(i, 1) <> 0 Then
                ElseIf Cells(i, 1) = Cells(i, 3) Then
                Cells(i, k).Interior.Color = RGB(255, 255, 0)
            End If

            If Cells(i, 2) <> 0 Then
                ElseIf Cells(i, 2) = Cells(i, 3) Then
                Cells(i, k).Interior.Color = RGB(255, 255, 0)
            End If
    Next
Next
End Sub

登入發表討論

直播研討會

{{ item.channelVendor }} {{ item.webinarstarted }} |

直播中

1 個回答

rogeryao

iT邦超人 7 級 ‧ 2021-02-15 14:32:18

最佳解答

Private Sub CommandButton1_Click()
'0 與 1 數字型態;只有 0 與 1
x = Range("A1").End(xlToRight).Column
RowNo = Range("A1").End(xlDown).Row
'
For y = 1 To RowNo
  If ((Application.Sum(Range(Cells(y, 1), Cells(y, x)))) <= (x - 2)) Then
    Range(Cells(y, 1), Cells(y, x)).Interior.Color = RGB(255, 255, 0)
  End If
Next y
End Sub

Private Sub CommandButton2_Click()
'0 與 1 字串型態
Dim SearchStr As String
x = Range("A1").End(xlToRight).Column
RowNo = Range("A1").End(xlDown).Row
'
For y = 1 To RowNo
SearchStr = "0"
  If (WorksheetFunction.CountIf(Range(Cells(y, 1), Cells(y, x)), SearchStr) >= 2) Then
    Range(Cells(y, 1), Cells(y, x)).Interior.Color = RGB(255, 255, 0)
  End If
Next y
End Sub