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 -----
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

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

.

__,_._,___

Tidak ada komentar:

Poskan Komentar