Format Cell as 2 different formats

[jlclyde]

I have a macro that I am adding a date to a cell with VBA and at the
same time I would like to change the format of just the date to red
and a little smaller. DtTxt is the date.

Here is my current code. Obviously this is used within a form.

Thanks,
Jay

Private Sub Promise_Click()
Dim Rng As Range
Set Rng = Selection
Rng.UnMerge
Set Rng = Rng.Resize(1, 1)
Rng = Rng.Value & " " & DtTxt
Set Rng = Rng.Resize(1, 5)
Rng.Merge

SelectIt 'This is another subroutine that I run
With Selection
.Interior.ColorIndex = 4
End With
End Sub

 

[FSt1]

hi
try something like this.....
Private Sub Promise_Click()
Dim Rng As Range
Dim DtTxt As String
DtTxt = [C1].Value
Set Rng = Selection
Rng.UnMerge
Set Rng = Rng.Resize(1, 1)
Rng = Rng.Value & " " & DtTxt
Set Rng = Rng.Resize(1, 5)
Rng.Merge
With ActiveCell.Characters(Start:=13, Length:=9).Font
.Size = 8
.ColorIndex = 3
End With
end sub

regards
FSt1

 

[FSt1]

forgot to mention....
for rng.value i used rng.value in the active cell
for DtTxt i converted todays date in b1 to text in c1 with
=text(B1,"mm/dd/yy")
that is how i got the start number and length number.
multple formats in a cell doesn't not work for formulas or true numbers.
text and numbers formated as text only.

Regards
FSt1

hi
try something like this.....
Private Sub Promise_Click()
Dim Rng As Range
Dim DtTxt As String
DtTxt = [C1].Value
Set Rng = Selection
Rng.UnMerge
Set Rng = Rng.Resize(1, 1)
Rng = Rng.Value & " " & DtTxt
Set Rng = Rng.Resize(1, 5)
Rng.Merge
With ActiveCell.Characters(Start:=13, Length:=9).Font
.Size = 8
.ColorIndex = 3
End With
end sub

regards
FSt1

I have a macro that I am adding a date to a cell with VBA and at the
same time I would like to change the format of just the date to red
and a little smaller. DtTxt is the date.

Here is my current code. Obviously this is used within a form.

Thanks,
Jay

Private Sub Promise_Click()
Dim Rng As Range
Set Rng = Selection
Rng.UnMerge
Set Rng = Rng.Resize(1, 1)
Rng = Rng.Value & " " & DtTxt
Set Rng = Rng.Resize(1, 5)
Rng.Merge

SelectIt 'This is another subroutine that I run
With Selection
.Interior.ColorIndex = 4
End With
End Sub

 

[jlclyde]

forgot to mention....
for rng.value i used rng.value in the active cell
for DtTxt i converted todays date in b1 to text in c1 with
=text(B1,"mm/dd/yy")
that is how i got the start number and length number.
multple formats in a cell doesn't not work for formulas or true numbers.
text and numbers formated as text only.

Regards
FSt1

hi
try something like this.....
Private Sub Promise_Click()
    Dim Rng As Range
    Dim DtTxt As String
    DtTxt = [C1].Value
    Set Rng = Selection
    Rng.UnMerge
    Set Rng = Rng.Resize(1, 1)
    Rng = Rng.Value & "    " & DtTxt
    Set Rng = Rng.Resize(1, 5)
    Rng.Merge
     With ActiveCell.Characters(Start:=13, Length:=9).Font
        .Size = 8
        .ColorIndex = 3
    End With
end sub

regards
FSt1

I have a macro that I am adding a date to a cell with VBA and at the
same time I would like to change the format of just the date to red
and a little smaller.  DtTxt is the date.
Here is my current code.  Obviously this is used within a form.
Thanks,
Jay
Private Sub Promise_Click()
    Dim Rng As Range
    Set Rng = Selection
    Rng.UnMerge
    Set Rng = Rng.Resize(1, 1)
    Rng = Rng.Value & "    " & DtTxt
    Set Rng = Rng.Resize(1, 5)
    Rng.Merge
    SelectIt 'This is another subroutine that I run
    With Selection
        .Interior.ColorIndex = 4
    End With
End Sub- Hide quoted text -

- Show quoted text -

Fst1,
I found a way to track down the spot I needed in the cell and here is
what I did. I used Search to find where the space was. This became
my starting poing for the Characters selection to change font size.
Thanks for the help.

Jay
Dim Rng As Range
Dim Start As Integer
Set Rng = Selection
Rng.UnMerge
Set Rng = Rng.Resize(1, 1)
Rng = Rng.Value & " " & DtTxt
DtTxt = ""
Start = Application.WorksheetFunction.Search(" ", Rng.Value, 1)
Rng.Characters(Start, Len(Rng.Value)).Font.Size = 14
Set Rng = Rng.Resize(1, 5)
Rng.Merge
SelectIt
With Selection
.Interior.ColorIndex = 4
End With

 

[Rick Rothstein]

VBA has the built-in InStr function which can be used (instead of the SEARCH
worksheet function) to find one or more characters within a larger piece of
text.

--
Rick (MVP - Excel)

forgot to mention....
for rng.value i used rng.value in the active cell
for DtTxt i converted todays date in b1 to text in c1 with
=text(B1,"mm/dd/yy")
that is how i got the start number and length number.
multple formats in a cell doesn't not work for formulas or true numbers.
text and numbers formated as text only.

Regards
FSt1

hi
try something like this.....
Private Sub Promise_Click()
Dim Rng As Range
Dim DtTxt As String
DtTxt = [C1].Value
Set Rng = Selection
Rng.UnMerge
Set Rng = Rng.Resize(1, 1)
Rng = Rng.Value & " " & DtTxt
Set Rng = Rng.Resize(1, 5)
Rng.Merge
With ActiveCell.Characters(Start:=13, Length:=9).Font
.Size = 8
.ColorIndex = 3
End With
end sub

regards
FSt1

I have a macro that I am adding a date to a cell with VBA and at the
same time I would like to change the format of just the date to red
and a little smaller. DtTxt is the date.
Here is my current code. Obviously this is used within a form.
Thanks,
Jay
Private Sub Promise_Click()
Dim Rng As Range
Set Rng = Selection
Rng.UnMerge
Set Rng = Rng.Resize(1, 1)
Rng = Rng.Value & " " & DtTxt
Set Rng = Rng.Resize(1, 5)
Rng.Merge
SelectIt 'This is another subroutine that I run
With Selection
.Interior.ColorIndex = 4
End With
End Sub- Hide quoted text -

- Show quoted text -

Fst1,
I found a way to track down the spot I needed in the cell and here is
what I did. I used Search to find where the space was. This became
my starting poing for the Characters selection to change font size.
Thanks for the help.

Jay
Dim Rng As Range
Dim Start As Integer
Set Rng = Selection
Rng.UnMerge
Set Rng = Rng.Resize(1, 1)
Rng = Rng.Value & " " & DtTxt
DtTxt = ""
Start = Application.WorksheetFunction.Search(" ", Rng.Value, 1)
Rng.Characters(Start, Len(Rng.Value)).Font.Size = 14
Set Rng = Rng.Resize(1, 5)
Rng.Merge
SelectIt
With Selection
.Interior.ColorIndex = 4
End With