ExcelBanter - Format Cell as 2 different formats

ExcelBanter (https://www.excelbanter.com/)

- Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)

- - Format Cell as 2 different formats (https://www.excelbanter.com/excel-discussion-misc-queries/233485-format-cell-2-different-formats.html)

Format Cell as 2 different formats

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

Format Cell as 2 different formats

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

"jlclyde" wrote:

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

Format Cell as 2 different formats

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

"FSt1" wrote:

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

"jlclyde" wrote:

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

Format Cell as 2 different formats

On Jun 10, 4:57*pm, FSt1 wrote:
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

"FSt1" wrote:
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

"jlclyde" wrote:

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

Format Cell as 2 different formats

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)

"jlclyde" wrote in message
...
On Jun 10, 4:57 pm, FSt1 wrote:
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

"FSt1" wrote:
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

"jlclyde" wrote:

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