I want to change text color of cells in excel with macros

Question

I have a macros that copied last cell value of group cells in a vertical column, e.g.,

Sub copy_down()
    Dim r As Range, rr As Range, N As Long
    N = Cells(Rows.Count, "A").End(xlUp).Row
    Set r = Range(Cells(1, "A"), Cells(N, "A")).SpecialCells(xlCellTypeBlanks)
    For Each rr In r
        rr.FillDown
    Next
End Sub

EDIT ( added one extra line), here it is:

Sub copy_down()
    Dim r As Range, rr As Range, N As Long
    N = Cells(Rows.Count, "A").End(xlUp).Row
    Set r = Range(Cells(1, "A"), Cells(N, "A")).SpecialCells(xlCellTypeBlanks)
    For Each rr In r
        rr.FillDown
    Next
    Cells(N + 1, "A").FillDown
End Sub

Please add one more function in this macro. I want to change the the text color of that cell which is copied with red, something like to add:

'change formatting to your liking:

formulaCell.Font.Bold = True
formulaCell.Font.Color = RGB(255, 0, 0)

I was forget to ask this in previous my previous question.

Answer 1

If you just want to change the cell(s) that have been filled down then add the formatting changes to rr as you progress through the loop.

Sub copy_down()
    Dim r As Range, rr As Range, N As Long
    N = Cells(Rows.Count, "A").End(xlUp).Row
    Set r = Range(Cells(1, "A"), Cells(N, "A")).SpecialCells(xlCellTypeBlanks)
    For Each rr In r
        with rr
            .FillDown
            .Font.Bold = True
            .Font.Color = RGB(255, 0, 0)
        end with
    Next
End Sub