Our Home Depot account rep does a database query to get all plants
listed as 1A quality for the weekly availability. If they are
blooming, she'll put a Bold, 26pt * at the end of the text within the
cell. She has to type this in each and every time, change it to bold
and 26pt. I need a way for her to type in something simple in the
adjacent column, run a macro, and it put the bold, 26pt * at the end
of the text. Let's just say that the plants are listed in Column F and
she wants to type in a "b" in column G. What would the macro look like
to see the "b", throw the asterik at the end of the cell in Column F,
and then clear contents of the cell in G?

This would work:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 7 Then
If Target.Value = "b" Then
Cells(Target.Row, 6).Value = Cells(Target.Row, 6).Value & " *"
With Cells(Target.Row, 6).Characters(Start:=Len(Cells(Target.Row,
6)), Length:=1).Font
.FontStyle = "Bold"
.Size = 26
End With
Target.Delete
End If
End If
End Sub

--
Olly

wrote in message
oups.com...
Our Home Depot account rep does a database query to get all plants
listed as 1A quality for the weekly availability. If they are
blooming, she'll put a Bold, 26pt * at the end of the text within the
cell. She has to type this in each and every time, change it to bold
and 26pt. I need a way for her to type in something simple in the
adjacent column, run a macro, and it put the bold, 26pt * at the end
of the text. Let's just say that the plants are listed in Column F and
she wants to type in a "b" in column G. What would the macro look like
to see the "b", throw the asterik at the end of the cell in Column F,
and then clear contents of the cell in G?

Why not add another column and format it as required (also make is just wide
enough to hold the asterisk)
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

wrote in message
oups.com...
Our Home Depot account rep does a database query to get all plants
listed as 1A quality for the weekly availability. If they are
blooming, she'll put a Bold, 26pt * at the end of the text within the
cell. She has to type this in each and every time, change it to bold
and 26pt. I need a way for her to type in something simple in the
adjacent column, run a macro, and it put the bold, 26pt * at the end
of the text. Let's just say that the plants are listed in Column F and
she wants to type in a "b" in column G. What would the macro look like
to see the "b", throw the asterik at the end of the cell in Column F,
and then clear contents of the cell in G?

Right click on the sheet tab and select view code. Put in code like this:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim l As Long
On Error GoTo ErrHandler
If Target.Count 1 Then Exit Sub
If Target.Column = 7 And Target.Row 1 Then
If LCase(Target.Value) = "b" Then
l = Len(Target.Offset(0, -1).Value)
If l 0 Then
Application.EnableEvents = False
With Target.Offset(0, -1)
.Value = .Value & "*"
With .Characters(l + 1, 1).Font
.Bold = True
.Size = 26
End With
End With
Target.ClearContents
End If
End If
End If
ErrHandler:
Application.EnableEvents = True
End Sub

If you would need this on multiple sheets in the workbook, make the workbook
a window (rather than full ) and right click on the blue caption bar and
select view code. Put this modified code in there (the ThisWorkbook module)

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim l As Long
On Error GoTo ErrHandler
If Target.Count 1 Then Exit Sub
If Target.Column = 7 And Target.Row 1 Then
If LCase(Target.Value) = "b" Then
l = Len(Target.Offset(0, -1).Value)
If l 0 Then
Application.EnableEvents = False
With Target.Offset(0, -1)
.Value = .Value & "*"
With .Characters(l + 1, 1).Font
.Bold = True
.Size = 26
End With
End With
Target.ClearContents
End If
End If
End If
ErrHandler:
Application.EnableEvents = True
End Sub

Don't have it in both places.


--
Regards,
Tom Ogilvy



" wrote:

Our Home Depot account rep does a database query to get all plants
listed as 1A quality for the weekly availability. If they are
blooming, she'll put a Bold, 26pt * at the end of the text within the
cell. She has to type this in each and every time, change it to bold
and 26pt. I need a way for her to type in something simple in the
adjacent column, run a macro, and it put the bold, 26pt * at the end
of the text. Let's just say that the plants are listed in Column F and
she wants to type in a "b" in column G. What would the macro look like
to see the "b", throw the asterik at the end of the cell in Column F,
and then clear contents of the cell in G?