根据日期和其他条件突出显示单元格 [英] Highlight cells based on date and other conditions

问题描述

我正在尝试根据某些条件在VBA中编码突出显示功能.我可以通过条件格式轻松地做到这一点，但是我注意到，如果用户剪切/粘贴(非特殊)/删除行/等.然后修改条件格式范围.我希望条件格式设置范围保持固定，而不是映射到实际单元格.如果有人知道该怎么做，或者保护条件格式，但仍然允许数据操作，那么此代码将是不必要的.

I am trying to code in VBA a highlighting function based on certain conditions. I can easily do this via conditional formatting, but I have noticed that if a user cuts/pastes (other than special)/deletes rows/etc. then the conditional formatting ranges are modified. I want the conditional formatting ranges to stay fixed rather than mapped to the actual cells. If anyone knows how to do that, or protect the conditional formatting but still allow data manipulation, then this code would be unnecessary.

我发现了两个我一直在尝试的不同代码，但是由于我是VBA的新手，所以我不太擅长此代码并遇到问题.我不知道该如何使用Isblank或Isempty功能.

I have found two different codes that I have been trying but since I'm new to VBA, I'm not very good at it and run into problems. I don't know how to use the Isblank or Isempty feature for one.

我需要用红色突出显示自现在起30天内的日期(包括通过的日期).我需要以黄色突出显示从现在起60天之前的日期，但是以黄色突出显示30多个日期.没有数据的单元格和超过60天的单元格必须保持突出状态.

I need to highlight dates that are earlier than 30 days from now (including dates passed) in red. I need to highlight dates that are earlier than 60 days from now but more than 30 in yellow. Cells without data and cells beyond 60 days out must remain unhighlighted.

任何帮助将不胜感激！

Private Sub Worksheet_Change(ByVal Target As Range)

Dim icolor As Integer

    If Not Intersect(Target, Range("C3:T65")) Is Nothing Then

        Select Case Target

            Case Is <= Date + 60

                icolor = 6

            Case Is <= Date + 30

                icolor = 3

            Case IsEmpty()

                icolor = 2

        End Select


        Target.Interior.ColorIndex = icolor

    End If

End Sub

其他选项:

Sub Highlight()
    Dim cell As Range

    For Each cell In Range("C3:T65")
        If cell.Value <= Date + 60 And cell.Value > Date + 30 Then
            cell.Offset(0, 1).Interior.ColorIndex = 6

        ElseIf cell.Value <= Date + 30 Then
            cell.Offset(0, 1).Interior.ColorIndex = 3

        ElseIf cell.Value IsEmpty() Then
            cell.Offset(0, 1).Interior.ColorIndex = 2

        End If
    Next cell

End Sub

推荐答案

您的代码都差不多了.以下两者的结合应该可以完成工作:

Both your codes are almost there. The following combination of the two should do the job:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim icolor As Integer
    Dim cell As Range

    If Intersect(Target, Range("C3:T65")) Is Nothing Then Exit Sub

    For Each cell In Target
        icolor = 0
        Select Case cell
            Case Is <= Date + 30: icolor = 3
            Case Is <= Date + 60: icolor = 6
            Case "": icolor = 2
        End Select
        If icolor <> 0 Then cell.Interior.ColorIndex = icolor
    Next cell
End Sub

这篇关于根据日期和其他条件突出显示单元格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！