如何为空单元格上色 [英] how to colors empty cells

问题描述

效果很好，但他没有使用标签的最后一行

it works good but he doesn't take the last line of the tab

您能帮我发现我的错误吗?

can u please help me to find my errors !

         Dim c As Range

将模糊范围作为范围

   For q = 2 To ActiveSheet.Cells(ActiveSheet.Rows.Count, 1).End(xlUp).Row

Set MaPlage = Range("A:H, J:R").Rows(q)
 For Each c In MaPlage.Cells
If Len(c.Value) = 0 Then c.Interior.Color = vbYellow

If CStr(ActiveSheet.Cells(q, 31).Value) = "Completed - Appointment made / Complété - Nomination faite" _
     And WorksheetFunction.CountIf(MaPlage, "") = 0 Then
    Select Case UCase(ActiveSheet.Cells(q, 14).Value)
        Case "INA_CIN"
            ActiveSheet.Cells(q, 42).Value = "XX"
    End Select
End If

Next c

下一个q

推荐答案

编辑:已更新以显示如何突出显示空白单元格

updated to show how to highlight empty cells

Dim MaPlage As Range, c As Range

Set MaPlage = Sheet1.Range(Replace("A#:H#,J#:R#", "#", q)) 

For Each c In MaPlage.Cells '<<EDIT2 to remove extra space
    If Len(c.Value) = 0 Then c.Interior.Color = vbRed
Next c

Sub TT()

    Const S As String = "Completed - Appointment made / Complété - Nomination faite"

    Dim MaPlage As Range, c As Range, rw As Range
    Dim q As Integer, blanks As Long

       For q = 2 To ActiveSheet.Cells(ActiveSheet.Rows.Count, 1).End(xlUp).Row

           Set rw = Sheet1.Rows(q)
           Set MaPlage = rw.Range("A1:H1,J1:R1")
           blanks = 0
           For Each c In MaPlage.Cells
               If Len(c.Value) = 0 Then
                  c.Interior.Color = vbRed
                  blanks = blanks + 1
               End If
           Next c

           If CStr(rw.Cells(31).Value) = S And blanks = 0 Then
               Select Case UCase(rw.Cells(14).Value)
                Case "INA_CIN"
                    rw.Cells(42).Value = "XX"
                End Select
           End If

    Next q
End Sub

这篇关于如何为空单元格上色的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！