EXCEL FEED: Re: [ExcelVBA] Adding Color to Concatenated Cells

Kamis, 16 April 2015

Re: [ExcelVBA] Adding Color to Concatenated Cells

Your specific problem is the "With ActiveCell". You are not moving the active cell as you go through your loop.

You could use "With Cells(i, 4)" to select the right place to change the colour.

However, the With is not needed at all. You can just put the Cells(i, 4) to the left of the .Characters.

This code will set the second character of the Column 4 cell to red, when you have a heart or diamond. However, this might not be what you want, depending on what is in Column B. If Column B is a single character, then fine, but if it's a number 1-13 then it'll set the wrong character.

Anyway

Public Sub ConcatCards()
Dim i As Integer
Columns(4).HorizontalAlignment = xlHAlignRight
For i = 1 To 52
    Cells(i, 4).Value = Cells(i, 2).Value & Cells(i, 3).Value
    If Right(Cells(i, 4), 1) = ChrW(&H2666) Or Right(Cells(i, 4), 1) = ChrW(&H2665) Then
      Cells(i, 4).Characters(Start:=2, Length:=1).Font.Color = vbRed
      'Cells(i, 5).Value = "Yes"
    End If
Next i
Columns("b:c").EntireColumn.Hidden = True
End Sub

Regards, Dave S

----- Original Message -----

From: jeff.flanagan@duffcorp.ca [ExcelVBA]

To: ExcelVBA@yahoogroups.com

Sent: Friday, April 17, 2015 12:10 PM

Subject: [ExcelVBA] Adding Color to Concatenated Cells

Hi All,

I have this simple problem that is driving me nuts. Basically I have card rank in column B and card suit in column C. I am concatenating the two into column D. I am trying to add color red to the concatenated hearts and diamonds. I have confirmed that I am identifying those suits with the commented out line, but I cannot apply the color to the suit. Any help is appreciated.

Public Sub ConcatCards()

Dim i As Integer

Columns(4).HorizontalAlignment = xlHAlignRight

For i = 1 To 52
    Cells(i, 4).Value = Cells(i, 2).Value & Cells(i, 3).Value

        If Right(Cells(i, 4), 1) = ChrW(&H2666) Or Right(Cells(i, 4), 1) = ChrW(&H2665) Then
            With ActiveCell
                .Characters(Start:=2, Length:=1).Font.Color = vbRed
            End With
            'Cells(i, 5).Value = "Yes"
        End If

Next i

Columns("b:c").EntireColumn.Hidden = True

End Sub

No virus found in this message.
Checked by AVG - www.avg.com
Version: 2015.0.5863 / Virus Database: 4331/9556 - Release Date: 04/16/15

__._,_.___

Posted by: "David Smart" <smartware.consulting@gmail.com>

Reply via web post

•

Reply to sender

•

Reply to group

•

Start a New Topic

•

Messages in this topic (2)

----------------------------------
Be sure to check out TechTrax Ezine for many, free Excel VBA articles! Go here: http://www.mousetrax.com/techtrax to enter the ezine, then search the ARCHIVES for EXCEL VBA.

----------------------------------
Visit our ExcelVBA group home page for more info and support files:
http://groups.yahoo.com/group/ExcelVBA

----------------------------------
More free tutorials and resources available at:
http://www.mousetrax.com

----------------------------------

Visit Your Group