3 Color Scale with Dates

[Newbienew]

Good Day Pros,

I wanted to know if there is a formula that allows you to highlight red, yellow, and Green in a gradient scheme but with dates. So if the beginning date is 1 jan 2025 and today's date is in between the 1st and the 15th it will be green. If today's date is over 15 days but less than 30 days is yellow and if its over 31 days its red. Is there a way to do this?

 

[fahad_ibnfurjan]

XL2bb can't copy gradient scheme so do it manualy

3 color.xlsx
	A	B	C	D	E	F	G
2	S	M	T	W	Th	F	S
3				1	2	3	4
4	5	6	7	8	9	10	11
5	12	13	14	15	16	17	18
6	19	20	21	22	23	24	25
7	26	27	28	29	30	31
8
3Color

Cell Formulas
Range	Formula
A3:G8	A3	=IFERROR(DAY(TEXT(DATE(2025,1,1),"yyyy-mm-")&TRANSPOSE(ROW(1:7)+1)+(ROW($1:$6)-1)*7-WEEKDAY(DATE(2025,1,1))),"")
Press CTRL+SHIFT+ENTER to enter array formulas.

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A3:G8	Expression	=AND(A3<>"",A3>30)	text	NO
A3:G8	Expression	=AND(A3>15,A3<30)	text	NO
A3:G8	Expression	=A3<16	text	NO

 

[fahad_ibnfurjan]

Sub Three_Color_Scale_with_Dates()
    
    
    With Range("A3:G8") '<<<<<
    .FormatConditions.Delete
        .FormatConditions.Add Type:=xlExpression, Formula1:="=A3<16"
        .FormatConditions(.FormatConditions.Count).SetFirstPriority
        With .FormatConditions(1).Interior
            .Pattern = xlPatternLinearGradient
            .Gradient.Degree = 90
            .Gradient.ColorStops.Clear
        End With
        With .FormatConditions(1).Interior.Gradient.ColorStops
        .Add(0).Color = 16711422
        .Add(0.5).Color = 5222144
        .Add(1).Color = 16711422
        End With
        .FormatConditions(1).StopIfTrue = False
        .FormatConditions.Add Type:=xlExpression, Formula1:="=AND(A3>15,A3<30)"
        .FormatConditions(.FormatConditions.Count).SetFirstPriority
        With .FormatConditions(1).Interior
            .Pattern = xlPatternLinearGradient
            .Gradient.Degree = 90
            .Gradient.ColorStops.Clear
        End With
        With .FormatConditions(1).Interior.Gradient.ColorStops
            .Add(0).Color = 16711422
            .Add(0.5).Color = 65278
            .Add(1).Color = 16711422
        End With
        .FormatConditions(1).StopIfTrue = False
        .FormatConditions.Add Type:=xlExpression, Formula1:="=AND(A3<>"""",A3>30)"
        .FormatConditions(.FormatConditions.Count).SetFirstPriority
        With .FormatConditions(1).Interior
            .Pattern = xlPatternLinearGradient
            .Gradient.Degree = 90
            .Gradient.ColorStops.Clear
        End With
        With .FormatConditions(1).Interior.Gradient.ColorStops
            .Add(0).Color = 16711422
            .Add(0.5).Color = 254
            .Add(1).Color = 16711422
        End With
        .FormatConditions(1).StopIfTrue = False
    
    End With
End Sub

 

[Newbienew]

I do apologize for the late response and feedback. I would love to use VBA, but most of my projects bounce between google sheets and Teams. The formula would need to work with the NOW() or Today() formula. So it would change automatically as the days go on.