Announcement

Collapse
No announcement yet.

VBA code to change cells colors based on date in other cells

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • VBA code to change cells colors based on date in other cells



    Looking for a VBA code:. (all 3 of my conditional formats have been used for different senarios)
    Cells "AE4" to "AE2000" contains dates.
    I would like adjacent cells "AF4" to "AF2000", to change "Yellow" when "Today's" date falls within a range of 3 to 7 seven days prior to "AE" date, and "Red" if "Today's" date falls within a range of 2 days prior to 365 days after "AE" date .

  • #2
    Re: VBA code to change cells colors based on date in other cells

    Try
    Code:
    Private Sub Worksheet_Activate()
    Dim MyRg As Range
    Dim F As Range
    Dim DateDiff As Long
        Application.ScreenUpdating = False
        Set MyRg = Range("AE1:AE" & Range("AE" & Rows.Count).End(xlUp).Row)
        For Each F In MyRg
            If ((F <> Empty) And IsDate(F)) Then
                DateDiff = Int(Date - F.Value)
                F.Offset(0, 1).Interior.ColorIndex = xlNone
                If ((DateDiff >= 3) And (DateDiff <= 7)) Then F.Offset(0, 1).Interior.ColorIndex = 6
                If ((DateDiff >= -365) And (DateDiff <= 2)) Then F.Offset(0, 1).Interior.ColorIndex = 3
    
                
            End If
        Next F
        Application.ScreenUpdating = True
    End Sub
    Attached Files
    Triumph without peril brings no glory: Just try

    Comment


    • #3
      Re: VBA code to change cells colors based on date in other cells

      Doesn't seem to work for me.
      When I enter a date anywhere in column AE nothing happens

      Comment


      • #4
        Re: VBA code to change cells colors based on date in other cells

        The update is done when openning the sheet.
        If you want to activate it when a date is entered use
        Code:
        Private Sub Worksheet_Change(ByVal Target As Range)
        Dim MyRg As Range
        Dim DateDiff As Long
            Set MyRg = Range("AE1:AE" & Range("AE" & Rows.Count).End(xlUp).Row)
            If (Intersect(Target, MyRg)) Then
                If (IsDate(Target)) Then
                    DateDiff = Int(Date - Target.Value)
                    Target.Offset(0, 1).Interior.ColorIndex = xlNone
                    If ((DateDiff >= 3) And (DateDiff <= 7)) Then Target.Offset(0, 1).Interior.ColorIndex = 6
                    If ((DateDiff >= -365) And (DateDiff <= 2)) Then Target.Offset(0, 1).Interior.ColorIndex = 3
                End If
            End If
        End Sub
        Attached Files
        Triumph without peril brings no glory: Just try

        Comment


        • #5
          Re: VBA code to change cells colors based on date in other cells

          I must be doing something wrong. Neither codes will work for me. Even after reopening the file.
          Could it be that I'm using Excel 2003?

          Comment


          • #6
            Re: VBA code to change cells colors based on date in other cells

            How is it working with the file attached?
            Did you Enable Macro when openning the file?
            It's working with Excel 2000.
            Triumph without peril brings no glory: Just try

            Comment


            • #7
              Re: VBA code to change cells colors based on date in other cells

              OK, got it working now, but when I enter data into cells not pertaining to the coded cells I get a Run Time Error box "Object Variable or With block variable not set"

              Comment


              • #8
                Re: VBA code to change cells colors based on date in other cells

                Ok, I was not good
                Code:
                Private Sub Worksheet_Change(ByVal Target As Range)
                Dim MyRg As Range
                Dim DateDiff As Long
                Dim Inter
                
                    Set MyRg = Range("AE1:AE" & Range("AE" & Rows.Count).End(xlUp).Row)
                    If Intersect(Target, MyRg) Is Nothing Then Exit Sub
                        If (IsDate(Target)) Then
                            DateDiff = Int(Date - Target.Value)
                            Target.Offset(0, 1).Interior.ColorIndex = xlNone
                            If ((DateDiff >= 3) And (DateDiff <= 7)) Then Target.Offset(0, 1).Interior.ColorIndex = 6
                            If ((DateDiff >= -365) And (DateDiff <= 2)) Then Target.Offset(0, 1).Interior.ColorIndex = 3
                        End If
                End Sub
                Triumph without peril brings no glory: Just try

                Comment


                • #9
                  Re: VBA code to change cells colors based on date in other cells

                  Thank You PCI. Everything works great now. Thanks for all the help.

                  Comment


                  • #10
                    Looking for a VBA code:. (i would like to change color of a part of a row, based on days. meaning when a day passed, the color of that particular row should change)

                    thanks

                    Comment


                    • #11


                      Open your own thread ....!
                      Triumph without peril brings no glory: Just try

                      Comment

                      Working...
                      X