Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
format cell with multiple formats for telephone numbers | Excel Discussion (Misc queries) | |||
Lock cell formats (conditional format) | Excel Worksheet Functions | |||
Conditional Format - Copying Formats | Excel Worksheet Functions | |||
add txf format to excel formats | Excel Discussion (Misc queries) | |||
number formats that allow you to format the appearance of negativ. | Charts and Charting in Excel |