modifying font color in sub dependent on cell value

Question

I'm trying to make a sub that copies and pastes a range of data and modifies some aspects of the formatting of the second range.

I want to make the color dependent on if the numbers are odd or even - here is what i wrote so far:

sub copy_paste_format()
Dim c As Variant
Dim SECONDARY()
Dim i As Integer
Dim n As Integer



ActiveCell.Offset(0, -5).Range("A1:E5").Select
ActiveWorkbook.Names.Add Name:="PRIMARY", RefersToR1C1:= _
    "=Sheet2!R1C1:R5C5"
Selection.Copy
ActiveCell.Offset(0, 5).Range("A1").Select
ActiveSheet.Paste
ActiveCell.Range("A1:E5").Select
Application.CutCopyMode = False
ActiveWorkbook.Names.Add Name:="SECONDARY", RefersToR1C1:= _
    "=Sheet2!R1C6:R5C10"
Selection.Font.Bold = True
With Selection.Font
    .Name = "Calibri"
    .Size = 14
 End With
n = SECONDARY.Count

For i = 1 To n
If Cells.Value Mod 2 = 0 Then
Cells.Font.Color = vbRed
Else: Cells.Font.Color = vblue
End If
Next i

End Sub

The part I'm having trouble with is the color formatting. At the moment I'm getting a compile error of an "invalid qualifier". But using SECONDARY.count in the for loop doesn't produce any better results.

Does anyone have any suggestions?

score 1 · Accepted Answer · edited May 23 '17 at 12:02

To refer to a Range using its name, use something like:

n = Range("SECONDARY").Count

To loop through each cell in your range, you would probably be better off doing something like:

Dim rng As Range
For Each rng In Range("SECONDARY").Cells
    If rng.Value Mod 2 = 0 Then
        rng.Font.Color = vbRed
    Else
        rng.Font.Color = vbBlue
    End If
Next

I couldn't work out what you are trying to do in the first part of your code, but I suggest you read up on how to avoid Select and Activate because I don't think it will be doing what you want it to be doing.

Ahh - I think I have worked out what it does. Assuming the active cell was, for instance, Sheet1!G12 the code is copying cells Sheet1!B12:F16 and pasting it into Sheet1!G12:K16. It then changes the font in cells Sheet1!G12:K16 to bold, Calibri, size 14. Mixed in with this code, two range names are created for Sheet2!A1:E5 and Sheet2!F1:J5.

I'm guessing that what you are really trying to do with your code is:

Sub copy_paste_format()
    Dim c As Range
    With Worksheets("Sheet2")
        .Range("A1:E5").Name = "PRIMARY"
        .Range("F1:J5").Name = "SECONDARY"
        .Range("PRIMARY").Copy .Range("SECONDARY")
        With .Range("SECONDARY").Font
            .Bold = True
            .Name = "Calibri"
            .Size = 14
        End With
        For Each c In .Range("SECONDARY").Cells
            If c.Value Mod 2 = 0 Then
                c.Font.Color = vbRed
            Else
                c.Font.Color = vbBlue
            End If
        Next
    End With
End Sub

Note: I'm not sure whether you are assigning the range names because you thought you had to, or whether you need them for some other purpose, so I left them in the code.

modifying font color in sub dependent on cell value

1 Answers1