Highlighting range when single cell in range is certain date with vba

[Legacy 259939]

I have a spreadhseet that I update and copy from multiple times a day. I am trying to automate as much formatting as possible with vba.

I want to highlight a#,j# if c# is a certain date. I have a rough idea of the formula for choosing date it is the code I am having trouble with. I found code to highlight the single cell but I want to highlight the a - j of that row, not the entire row.

I have 3 sets of dates I am looking for:
if c#=today then a#,j#.interior=yellow
if c# <today and >=(today-7) then a#,j#.interior=light red
if c# <(today-7) then a#,j#.interior=dark red with white text

I know conditional formatting will do this but when the data is copied and pasted somewhere else the formatting does not come with it.

Thanks!

 

[mumps]

I assume that by

c# <today and="">=(today-7) then a#,j#.interior=light red
</today>

you mean a date between today and seven days ago. Try this macro. It applies only to row 1.

Sub FillRange()
    If Range("C1") = Date Then
        Range("A1:J1").Interior.ColorIndex = 6
        Range("A1:J1").Font.Color = vbBlack
    ElseIf Range("C1") >= Date - 7 And Range("C1") <= Date Then
        Range("A1:J1").Interior.ColorIndex = 3
        Range("A1:J1").Font.Color = vbBlack
    ElseIf Range("C1") < Date - 7 Then
        Range("A1:J1").Interior.ColorIndex = 3
        Range("A1:J1").Font.Color = vbWhite
    End If
End Sub

 

[Legacy 259939]

That did work. Now how do I get it do go down and do the same for each row that has data?

 

[mumps]

Try:

Sub FillRange()
    Application.ScreenUpdating = False
    Dim rng As Range
    Dim bottomC As Long
    bottomC = Range("C" & Rows.Count).End(xlUp).Row
    For Each rng In Range("C2:C" & bottomC)
        If rng = Date Then
            Range(Cells(rng.Row, 1), Cells(rng.Row, 10)).Interior.ColorIndex = 6
            Range(Cells(rng.Row, 1), Cells(rng.Row, 10)).Font.Color = vbBlack
        ElseIf rng >= Date - 7 And Range("C1") <= Date Then
            Range(Cells(rng.Row, 1), Cells(rng.Row, 10)).Interior.ColorIndex = 22
            Range(Cells(rng.Row, 1), Cells(rng.Row, 10)).Font.Color = vbBlack
        ElseIf rng < Date - 7 Then
            Range(Cells(rng.Row, 1), Cells(rng.Row, 10)).Interior.ColorIndex = 3
            Range(Cells(rng.Row, 1), Cells(rng.Row, 10)).Font.Color = vbWhite
        End If
    Next rng
    Application.ScreenUpdating = True
End Sub

 

[Legacy 259939]

Thank you so much :D This worked great!

 

[mumps]

My pleasure.