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 .
VBA code to change cells colors based on date in other cells
-
-
-
Re: VBA code to change cells colors based on date in other cells
Try
Code
Display MorePrivate 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
-
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 -
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 useCode
Display MorePrivate 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
-
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? -
-
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. -
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"
-
Re: VBA code to change cells colors based on date in other cells
Ok, I was not good
Code
Display MorePrivate 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
-
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.
-
[h=2]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)[/h] thanks
-
-
Open your own thread ....!
Participate now!
Don’t have an account yet? Register yourself now and be a part of our community!