Prev: Classification
Next: Create a small table from a large table by skipping empty colu
From: Gladiator on 23 Apr 2010 12:25
Rick, i could not get it to work.

"Rick Rothstein" wrote:

> Only text strings constants can have different formatting for a portion of
> its characters and, unfortunately, formulas do not produce text string
> constants. If you are able to use VB code, we can mimic the dynamic action
> of a formula while at the same time putting text string constants with
> individualized character formatting in specified cells. Right click the tab
> at the bottom of the worksheet where you want this functionality, click View
> Code from the popup menu that appears and copy/paste only ONE of the
> following two into the code window that appeared (read the comments above
> the line of equal signs to see which one to use)...
>
> 'If the date in A1 is typed in (that is, A1 does NOT contain a formula)
> '========================================================================
> Private Sub Worksheet_Change(ByVal Target As Range)
> Const ResultRange As String = "E4"
> Const RangeToMonitor As String = "A1"
> If Not Intersect(Target, Range(RangeToMonitor)) Is Nothing Then
> Range(ResultRange).Value = "Mission 1 Start Date: " & _
> Format(Target.Value, "DD-MMM-YYYY")
> Range(ResultRange).Characters(23, 11).Font.Bold = True
> End If
> End Sub
>
> 'If A1 contains a formula which display the date
> '================================================
> Private Sub Worksheet_Change(ByVal Target As Range)
> Const ResultRange As String = "E4"
> Const MonitorRange As String = "A1"
> If Not Intersect(Target, Range(MonitorRange).Precedents) Is Nothing Then
> Range(ResultRange).Value = "Mission 1 Start Date: " & _
> Format(Range(MonitorRange).Value, "DD-MMM-YYYY")
> Range(ResultRange).Characters(23, 11).Font.Bold = True
> End If
> End Sub
>
> Note: You have to change at least one of the assignments in whichever of the
> above code modules you use. These values are in the first two lines of each
> code module and they begin with the keyword "Const". The one named
> ResultRange, where I have assigned "E4" to it, is the address where your
> "Mission 1 Start Date" formula is in... change the "E4" I used to your
> actual cell's address. The one named MonitorRange, where I have assigned
> "A1" to it, is the address where the date your "Mission 1 Start Date"
> formula references... A1 is what you told us in your posting, but you can
> change it if A1 was an example and not the actual cell containing the date
> that you want to be bolded in the ResultRange cell.
>
> --
> Rick (MVP - Excel)
>
>
>
> "Gladiator" <Gladiator(a)discussions.microsoft.com> wrote in message
> news:7E825FD9-C276-43CF-BFB4-A7F3D6FB932E(a)microsoft.com...
> > Hi all,
> > In MS Excel, in th below formula what additional formula do I add to make
> > selective text in different formats (ex. bold)?
> >
> > Ex: ="Mission 1 Start Date: "&text(A1,"DD-MMM-YYYY")
> >
> > In the result I want only the date part of the result text to be in bold
> > letters.
> > Thanks in advance.
>
> .
>
From: Rick Rothstein on 23 Apr 2010 12:59
Tell me what is in A1... a manually entered date or a formula that displays
a date. If a manually entered date, what is the Cell Format for that date?
If a formula, copy/paste it in your response so we can see what you are
working with.

--
Rick (MVP - Excel)



"Gladiator" <Gladiator(a)discussions.microsoft.com> wrote in message
news:EA3FF42F-FBAE-4B1F-BC60-C3AF8FFA4B2C(a)microsoft.com...
> Rick, i could not get it to work.
>
> "Rick Rothstein" wrote:
>
>> Only text strings constants can have different formatting for a portion
>> of
>> its characters and, unfortunately, formulas do not produce text string
>> constants. If you are able to use VB code, we can mimic the dynamic
>> action
>> of a formula while at the same time putting text string constants with
>> individualized character formatting in specified cells. Right click the
>> tab
>> at the bottom of the worksheet where you want this functionality, click
>> View
>> Code from the popup menu that appears and copy/paste only ONE of the
>> following two into the code window that appeared (read the comments above
>> the line of equal signs to see which one to use)...
>>
>> 'If the date in A1 is typed in (that is, A1 does NOT contain a formula)
>> '========================================================================
>> Private Sub Worksheet_Change(ByVal Target As Range)
>> Const ResultRange As String = "E4"
>> Const RangeToMonitor As String = "A1"
>> If Not Intersect(Target, Range(RangeToMonitor)) Is Nothing Then
>> Range(ResultRange).Value = "Mission 1 Start Date: " & _
>> Format(Target.Value, "DD-MMM-YYYY")
>> Range(ResultRange).Characters(23, 11).Font.Bold = True
>> End If
>> End Sub
>>
>> 'If A1 contains a formula which display the date
>> '================================================
>> Private Sub Worksheet_Change(ByVal Target As Range)
>> Const ResultRange As String = "E4"
>> Const MonitorRange As String = "A1"
>> If Not Intersect(Target, Range(MonitorRange).Precedents) Is Nothing
>> Then
>> Range(ResultRange).Value = "Mission 1 Start Date: " & _
>> Format(Range(MonitorRange).Value, "DD-MMM-YYYY")
>> Range(ResultRange).Characters(23, 11).Font.Bold = True
>> End If
>> End Sub
>>
>> Note: You have to change at least one of the assignments in whichever of
>> the
>> above code modules you use. These values are in the first two lines of
>> each
>> code module and they begin with the keyword "Const". The one named
>> ResultRange, where I have assigned "E4" to it, is the address where your
>> "Mission 1 Start Date" formula is in... change the "E4" I used to your
>> actual cell's address. The one named MonitorRange, where I have assigned
>> "A1" to it, is the address where the date your "Mission 1 Start Date"
>> formula references... A1 is what you told us in your posting, but you can
>> change it if A1 was an example and not the actual cell containing the
>> date
>> that you want to be bolded in the ResultRange cell.
>>
>> --
>> Rick (MVP - Excel)
>>
>>
>>
>> "Gladiator" <Gladiator(a)discussions.microsoft.com> wrote in message
>> news:7E825FD9-C276-43CF-BFB4-A7F3D6FB932E(a)microsoft.com...
>> > Hi all,
>> > In MS Excel, in th below formula what additional formula do I add to
>> > make
>> > selective text in different formats (ex. bold)?
>> >
>> > Ex: ="Mission 1 Start Date: "&text(A1,"DD-MMM-YYYY")
>> >
>> > In the result I want only the date part of the result text to be in
>> > bold
>> > letters.
>> > Thanks in advance.
>>
>> .
>>
First  |  Prev  | 
Pages: 1 2
Prev: Classification
Next: Create a small table from a large table by skipping empty colu