vba excel如何检查单元格是否为空并为整行着色？ [英] vba excel how to check if cell not empty and color the entire row?

问题描述

我有VBA代码，用于比较用户输入的日期和当前日期，并用适当的颜色填充背景。一切正常。

i have VBA code that compare dates of the user input with the current dates and fill the background by the appropriate color. all it works fine.

现在我需要让系统检查所选行列 F 中的单元格是否为不是空的我需要将灰色的 D，E，F 列着色。

now i need to make the system to check if the cell in column F of the selected row is not empty i need to colored the column D,E,F in gray color.

Private Sub CommandButton1_Click()

    Dim i As Integer

    For i = Range("C5000").End(xlUp).Row To 2 Step -1 'Range upto 5000, chnge this as per your requirment'

        If IsEmpty(Cells(i, 3)) Then
            Cells(i, 3).Interior.Color = xlNone

        ElseIf (VBA.CDate(Cells(i, 3)) - VBA.Date()) < 0 Then
            Cells(i, 3).Interior.Color = vbGreen

        ElseIf (VBA.CDate(Cells(i, 3)) - VBA.Date()) = 0 Then
            Cells(i, 3).Interior.Color = vbYellow

        ElseIf (VBA.CDate(Cells(i, 3)) - VBA.Date()) >= 1 And (VBA.CDate(Cells(i, 3)) - VBA.Date()) <= 4 Then
            Cells(i, 3).Interior.Color = vbRed

        ElseIf (VBA.CDate(Cells(i, 3)) - VBA.Date()) >= 5 And (VBA.CDate(Cells(i, 3)) - VBA.Date()) <= 10 Then
            Cells(i, 3).Interior.Color = vbCyan

        Else
            Cells(i, 3).Interior.ColorIndex = xlNone

        End If

    Next

End Sub

我将不胜感激任何帮助

推荐答案

您还需要检查修剪（范围（F和i）.Value） <>相关标准中的。

我还修改了当前代码的逻辑，使其更短更快地运行（切换）至选择案例等。）

I've also modifed the logic of your current code to be shorter and faster to run through (switched to Select Case, etc.)

代码

Private Sub CommandButton1_Click()

Dim i As Long
Dim NumofDays As Long

For i = Range("C5000").End(xlUp).Row To 2 Step -1 'Range upto 5000, chnge this as per your requirment'
    Cells(i, 3).Interior.Color = xlNone ' set as inital color, only change if all criterias are met

    NumofDays = CDate(Cells(i, 3)) - Date

    Select Case NumofDays
        Case Is < 0
            Cells(i, 3).Interior.Color = vbGreen

        Case 0
            Cells(i, 3).Interior.Color = vbYellow

        Case 1 To 4
            Cells(i, 3).Interior.Color = vbRed

        Case 5 To 10
            Cells(i, 3).Interior.Color = vbCyan

    End Select

    ' your 2nd criteria to color the entire row if "F" is not empty
    If Trim(Range("F" & i).Value) <> "" Then Range("D" & i & ":F" & i).Interior.Color = ... ' selectyourcolor 
Next i

End Sub

这篇关于vba excel如何检查单元格是否为空并为整行着色？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！