Highlight a cell and a message box

  • Thread starter Thread starter Heather
  • Start date Start date
H

Heather

is there a way to highlight this cell as well and ask the user for how much
they would like to discount?
' Formats the Discount Percentage
.Range("H1").Select
Selection.NumberFormat = "0.00%"

Thank you!
 
Selecting the cell doesn't "highlight" it enough for you? If not, then you
need to tell us what or how your want the highlight to look. As for asking
the user for the discount amount, you can use an InputBox. For example,
perhaps like this...

..Range("H1").Value = InputBox("Discount amount?")
 
currently I only know how to get it to be in discount form? I don't know how
to add to the format to include highlighting the cell?
 
Explain what you are looking for when you say "highlighting the cell"...
exactly what do you have in mind here?
 
Hi Rick, thank you for helping :> I would like to format the cell [H1] so
that it's yellow with a percentage in it that can be multiplied by the values
in the rest of the worksheet

so for example
Input box asks what the discount is: They type in 10, but for some reason
it's then returning 1000% and I am hoping it'll place a 10% in there and the
background show up in Yellow
 
First off, you are putting the value 10 into the cell *first* and then
telling Excel to make that into a percentage... 10, as a percentage is
1000%. If your users will be typing in the actual percentage number, then
you will need to divide it by 100 to make it a correct value. You will have
to build the appropriate controlling code around this, but here is how to
ask for the percentage, insert it into the cell and then color the cell
yellow...

Range("H1").Value = InputBox("What percentage") / 100
Range("H1").NumberFormat = "0.00%"
Range("H1").Interior.ColorIndex = 6

--
Rick (MVP - Excel)


Heather said:
Hi Rick, thank you for helping :> I would like to format the cell [H1] so
that it's yellow with a percentage in it that can be multiplied by the
values
in the rest of the worksheet

so for example
Input box asks what the discount is: They type in 10, but for some reason
it's then returning 1000% and I am hoping it'll place a 10% in there and
the
background show up in Yellow

Rick Rothstein said:
Explain what you are looking for when you say "highlighting the cell"...
exactly what do you have in mind here?
Click to expand...
Click to expand...
 
That works awesome -- thank you! One last question

Now that I have this working for this tab I noticed that when I hit the
macro button in another tab it's formatting that tab on not this new tab?
How do I add the right coding to this so that it formats the [TermSummary]
tab not the [SourceCode] tab?

Thanks again -- this is a huge help!!! :>
Sub CreateTerm()
Dim ws As Worksheet
Set ws = gettab("TermSummary")
Dim rPart As Range
Dim Target As Range
With ws

' Formats the Column widths
Columns("B:B").ColumnWidth = 20
Columns("C:C").ColumnWidth = 30
' Formats the Currency
.Range("D:D").Select
Selection.NumberFormat = "#,##0_);[Red](#,##0)"
' Enters the Discount Allocation
.Range("G1") = "Apply Discount"
.Range("H1").Value = InputBox("What percentage") / 100
.Range("H1").NumberFormat = "0.00%"
.Range("H1").Interior.ColorIndex = 6
' Enters the Currency
.Range("B3") = Worksheets("Source Code").Range("A4")
.Range("C3") = Worksheets("Source Code").Range("B4")
' Enters the # of Users
.Range("B4") = "Users"
.Range("C4") = Worksheets("Source Code").Range("B5")
' Enters the Platform Type
.Range("B5") = "Platform/Edition"
.Range("C5") = Worksheets("Source Code").Range("A12")
.Range("D5") = Worksheets("Source Code").Range("B12")
' Enters the Addtl part numbers
Set Target = .Range("B6")
End With
Set rPart = Worksheets("Source Code").Range("B17")
Do Until rPart = ""
Target.Offset(, 1) = rPart.Offset(, -1).Value
Target.Offset(, 2) = rPart.Offset(, 1)
Set Target = Target.Offset(1)
Set rPart = rPart.Offset(1)
Loop

' Enters the Grand Total Price
Target.Offset(, 0) = "Term Model Price"
Target.Offset(, 2) = Worksheets("Source Code").Range("TermModel.Price")
Set Target = Target.Offset(1)
Set rPart = rPart.Offset(1)

' Enters the SW tools
Target.Offset(, 0) = "SW Tools"
Target.Offset(, 2) = Worksheets("SW Tools").Range("B14")



'Application.DisplayAlerts = False
'ws.Delete
'Application.DisplayAlerts = True
'Set rPart = Worksheets("Source Code").Range("B17")


' Start Word Doc
' ws.Activate
' DumptoWord1


End Sub


Rick Rothstein said:
First off, you are putting the value 10 into the cell *first* and then
telling Excel to make that into a percentage... 10, as a percentage is
1000%. If your users will be typing in the actual percentage number, then
you will need to divide it by 100 to make it a correct value. You will have
to build the appropriate controlling code around this, but here is how to
ask for the percentage, insert it into the cell and then color the cell
yellow...

Range("H1").Value = InputBox("What percentage") / 100
Range("H1").NumberFormat = "0.00%"
Range("H1").Interior.ColorIndex = 6

--
Rick (MVP - Excel)


Heather said:
Hi Rick, thank you for helping :> I would like to format the cell [H1] so
that it's yellow with a percentage in it that can be multiplied by the
values
in the rest of the worksheet

so for example
Input box asks what the discount is: They type in 10, but for some reason
it's then returning 1000% and I am hoping it'll place a 10% in there and
the
background show up in Yellow

Rick Rothstein said:
Explain what you are looking for when you say "highlighting the cell"...
exactly what do you have in mind here?

--
Rick (MVP - Excel)


currently I only know how to get it to be in discount form? I don't
know
how
to add to the format to include highlighting the cell?

:

Selecting the cell doesn't "highlight" it enough for you? If not, then
you
need to tell us what or how your want the highlight to look. As for
asking
the user for the discount amount, you can use an InputBox. For
example,
perhaps like this...

..Range("H1").Value = InputBox("Discount amount?")

--
Rick (MVP - Excel)


is there a way to highlight this cell as well and ask the user for
how
much
they would like to discount?
' Formats the Discount Percentage
.Range("H1").Select
Selection.NumberFormat = "0.00%"

Thank you!
Click to expand...
Click to expand...
Click to expand...
 
Are you saying that your SourceCode worksheet is being changed? A fast
reading of your code makes that seem unlikely... I don't see any code that
directly changes anything on the SourceCode sheet. Well, perhaps one thing;
this code...

Columns("B:B").ColumnWidth = 20
Columns("C:C").ColumnWidth = 30

will change the column width on the active worksheet because it does not
have a dot in front of the Columns property... if you put a dot in front of
them, then they will refer back to the WS sheet; otherwise, all the rest of
your code appears to be changing only the TermSummary sheet.

I do want to point out one thing about your code structure - these lines...

..Range("D:D").Select
Selection.NumberFormat = "#,##0_);[Red](#,##0)"

can be rewritten like this...

..Range("D:D").NumberFormat = "#,##0_);[Red](#,##0)"

Perhaps this previous posting of mine (a response to another person using
Select/Selection type constructions) will be of some help to you in your
future programming...

Whenever you see code constructed like this...

Range("A1").Select
Selection.<whatever>

you can almost always do this instead...

Range("A1").<whatever>

In your particular case, you have this...

Range("C2:C8193").Select 'select cells to export
For Each r In Selection.Rows

which, using the above concept, can be reduced to this...

For Each r In Range("C2:C8193").Rows

Notice, all I have done is replace Selection with the range you Select(ed)
in the previous statement and eliminate the process of doing any
Select(ion)s. Stated another way, the Selection produced from
Range(...).Select is a range and, of course, Range(...) is a range... and,
in fact, they are the same range, so it doesn't matter which one you use.
The added benefit of not selecting ranges first is your active cell does not
change.


--
Rick (MVP - Excel)


Heather said:
That works awesome -- thank you! One last question

Now that I have this working for this tab I noticed that when I hit the
macro button in another tab it's formatting that tab on not this new tab?
How do I add the right coding to this so that it formats the [TermSummary]
tab not the [SourceCode] tab?

Thanks again -- this is a huge help!!! :>
Sub CreateTerm()
Dim ws As Worksheet
Set ws = gettab("TermSummary")
Dim rPart As Range
Dim Target As Range
With ws

' Formats the Column widths
Columns("B:B").ColumnWidth = 20
Columns("C:C").ColumnWidth = 30
' Formats the Currency
.Range("D:D").Select
Selection.NumberFormat = "#,##0_);[Red](#,##0)"
' Enters the Discount Allocation
.Range("G1") = "Apply Discount"
.Range("H1").Value = InputBox("What percentage") / 100
.Range("H1").NumberFormat = "0.00%"
.Range("H1").Interior.ColorIndex = 6
' Enters the Currency
.Range("B3") = Worksheets("Source Code").Range("A4")
.Range("C3") = Worksheets("Source Code").Range("B4")
' Enters the # of Users
.Range("B4") = "Users"
.Range("C4") = Worksheets("Source Code").Range("B5")
' Enters the Platform Type
.Range("B5") = "Platform/Edition"
.Range("C5") = Worksheets("Source Code").Range("A12")
.Range("D5") = Worksheets("Source Code").Range("B12")
' Enters the Addtl part numbers
Set Target = .Range("B6")
End With
Set rPart = Worksheets("Source Code").Range("B17")
Do Until rPart = ""
Target.Offset(, 1) = rPart.Offset(, -1).Value
Target.Offset(, 2) = rPart.Offset(, 1)
Set Target = Target.Offset(1)
Set rPart = rPart.Offset(1)
Loop

' Enters the Grand Total Price
Target.Offset(, 0) = "Term Model Price"
Target.Offset(, 2) = Worksheets("Source Code").Range("TermModel.Price")
Set Target = Target.Offset(1)
Set rPart = rPart.Offset(1)

' Enters the SW tools
Target.Offset(, 0) = "SW Tools"
Target.Offset(, 2) = Worksheets("SW Tools").Range("B14")



'Application.DisplayAlerts = False
'ws.Delete
'Application.DisplayAlerts = True
'Set rPart = Worksheets("Source Code").Range("B17")


' Start Word Doc
' ws.Activate
' DumptoWord1


End Sub


Rick Rothstein said:
First off, you are putting the value 10 into the cell *first* and then
telling Excel to make that into a percentage... 10, as a percentage is
1000%. If your users will be typing in the actual percentage number, then
you will need to divide it by 100 to make it a correct value. You will
have
to build the appropriate controlling code around this, but here is how to
ask for the percentage, insert it into the cell and then color the cell
yellow...

Range("H1").Value = InputBox("What percentage") / 100
Range("H1").NumberFormat = "0.00%"
Range("H1").Interior.ColorIndex = 6

--
Rick (MVP - Excel)


Heather said:
Hi Rick, thank you for helping :> I would like to format the cell [H1]
so
that it's yellow with a percentage in it that can be multiplied by the
values
in the rest of the worksheet

so for example
Input box asks what the discount is: They type in 10, but for some
reason
it's then returning 1000% and I am hoping it'll place a 10% in there
and
the
background show up in Yellow

:

Explain what you are looking for when you say "highlighting the
cell"...
exactly what do you have in mind here?

--
Rick (MVP - Excel)


currently I only know how to get it to be in discount form? I don't
know
how
to add to the format to include highlighting the cell?

:

Selecting the cell doesn't "highlight" it enough for you? If not,
then
you
need to tell us what or how your want the highlight to look. As for
asking
the user for the discount amount, you can use an InputBox. For
example,
perhaps like this...

..Range("H1").Value = InputBox("Discount amount?")

--
Rick (MVP - Excel)


is there a way to highlight this cell as well and ask the user
for
how
much
they would like to discount?
' Formats the Discount Percentage
.Range("H1").Select
Selection.NumberFormat = "0.00%"

Thank you!
Click to expand...
Click to expand...
Click to expand...
 
Heather,
I think you formatting problem may be due to you omitting the period (full
stops) in front of these two lines of code:

..Columns("B:B").ColumnWidth = 20
..Columns("C:C").ColumnWidth = 30

also, I would suggest that you add some error checking after your inputbox
to enure numeric data only has been entered & to handle the cancel button
being pressed.

Something along the lines of following may work for you:



Dim discount As Variant
discount = InputBox("Discount amount?") / 100

If Not IsNumeric(discount) = False Then

With .Range("H1")

.NumberFormat = "0.00%"

.Value = discount

With .Interior
.ColorIndex = 6
.Pattern = xlSolid
End With

End With

Else

With .Range("H1")

.Value = 0

.Interior.ColorIndex = 2

End With

Exit Sub

End If

hope helpful

--
jb


Heather said:
That works awesome -- thank you! One last question

Now that I have this working for this tab I noticed that when I hit the
macro button in another tab it's formatting that tab on not this new tab?
How do I add the right coding to this so that it formats the [TermSummary]
tab not the [SourceCode] tab?

Thanks again -- this is a huge help!!! :>
Sub CreateTerm()
Dim ws As Worksheet
Set ws = gettab("TermSummary")
Dim rPart As Range
Dim Target As Range
With ws

' Formats the Column widths
Columns("B:B").ColumnWidth = 20
Columns("C:C").ColumnWidth = 30
' Formats the Currency
.Range("D:D").Select
Selection.NumberFormat = "#,##0_);[Red](#,##0)"
' Enters the Discount Allocation
.Range("G1") = "Apply Discount"
.Range("H1").Value = InputBox("What percentage") / 100
.Range("H1").NumberFormat = "0.00%"
.Range("H1").Interior.ColorIndex = 6
' Enters the Currency
.Range("B3") = Worksheets("Source Code").Range("A4")
.Range("C3") = Worksheets("Source Code").Range("B4")
' Enters the # of Users
.Range("B4") = "Users"
.Range("C4") = Worksheets("Source Code").Range("B5")
' Enters the Platform Type
.Range("B5") = "Platform/Edition"
.Range("C5") = Worksheets("Source Code").Range("A12")
.Range("D5") = Worksheets("Source Code").Range("B12")
' Enters the Addtl part numbers
Set Target = .Range("B6")
End With
Set rPart = Worksheets("Source Code").Range("B17")
Do Until rPart = ""
Target.Offset(, 1) = rPart.Offset(, -1).Value
Target.Offset(, 2) = rPart.Offset(, 1)
Set Target = Target.Offset(1)
Set rPart = rPart.Offset(1)
Loop

' Enters the Grand Total Price
Target.Offset(, 0) = "Term Model Price"
Target.Offset(, 2) = Worksheets("Source Code").Range("TermModel.Price")
Set Target = Target.Offset(1)
Set rPart = rPart.Offset(1)

' Enters the SW tools
Target.Offset(, 0) = "SW Tools"
Target.Offset(, 2) = Worksheets("SW Tools").Range("B14")



'Application.DisplayAlerts = False
'ws.Delete
'Application.DisplayAlerts = True
'Set rPart = Worksheets("Source Code").Range("B17")


' Start Word Doc
' ws.Activate
' DumptoWord1


End Sub


Rick Rothstein said:
First off, you are putting the value 10 into the cell *first* and then
telling Excel to make that into a percentage... 10, as a percentage is
1000%. If your users will be typing in the actual percentage number, then
you will need to divide it by 100 to make it a correct value. You will have
to build the appropriate controlling code around this, but here is how to
ask for the percentage, insert it into the cell and then color the cell
yellow...

Range("H1").Value = InputBox("What percentage") / 100
Range("H1").NumberFormat = "0.00%"
Range("H1").Interior.ColorIndex = 6

--
Rick (MVP - Excel)


Heather said:
Hi Rick, thank you for helping :> I would like to format the cell [H1] so
that it's yellow with a percentage in it that can be multiplied by the
values
in the rest of the worksheet

so for example
Input box asks what the discount is: They type in 10, but for some reason
it's then returning 1000% and I am hoping it'll place a 10% in there and
the
background show up in Yellow

:

Explain what you are looking for when you say "highlighting the cell"...
exactly what do you have in mind here?

--
Rick (MVP - Excel)


currently I only know how to get it to be in discount form? I don't
know
how
to add to the format to include highlighting the cell?

:

Selecting the cell doesn't "highlight" it enough for you? If not, then
you
need to tell us what or how your want the highlight to look. As for
asking
the user for the discount amount, you can use an InputBox. For
example,
perhaps like this...

..Range("H1").Value = InputBox("Discount amount?")

--
Rick (MVP - Excel)


is there a way to highlight this cell as well and ask the user for
how
much
they would like to discount?
' Formats the Discount Percentage
.Range("H1").Select
Selection.NumberFormat = "0.00%"

Thank you!
Click to expand...
Click to expand...
Click to expand...
 
thank you John :) that's a big help. What about when I try to format Column
D in [Term Summary] -- it works when I walk thru in thru step thru, but not
when I hit the macro it stops on the other page?

' Formats the Currency
.Range("D:D").Select
Selection.NumberFormat = "#,##0_);[Red](#,##0)"


john said:
Heather,
I think you formatting problem may be due to you omitting the period (full
stops) in front of these two lines of code:

.Columns("B:B").ColumnWidth = 20
.Columns("C:C").ColumnWidth = 30

also, I would suggest that you add some error checking after your inputbox
to enure numeric data only has been entered & to handle the cancel button
being pressed.

Something along the lines of following may work for you:



Dim discount As Variant
discount = InputBox("Discount amount?") / 100

If Not IsNumeric(discount) = False Then

With .Range("H1")

.NumberFormat = "0.00%"

.Value = discount

With .Interior
.ColorIndex = 6
.Pattern = xlSolid
End With

End With

Else

With .Range("H1")

.Value = 0

.Interior.ColorIndex = 2

End With

Exit Sub

End If

hope helpful

--
jb


Heather said:
That works awesome -- thank you! One last question

Now that I have this working for this tab I noticed that when I hit the
macro button in another tab it's formatting that tab on not this new tab?
How do I add the right coding to this so that it formats the [TermSummary]
tab not the [SourceCode] tab?

Thanks again -- this is a huge help!!! :>
Sub CreateTerm()
Dim ws As Worksheet
Set ws = gettab("TermSummary")
Dim rPart As Range
Dim Target As Range
With ws

' Formats the Column widths
Columns("B:B").ColumnWidth = 20
Columns("C:C").ColumnWidth = 30
' Formats the Currency
.Range("D:D").Select
Selection.NumberFormat = "#,##0_);[Red](#,##0)"
' Enters the Discount Allocation
.Range("G1") = "Apply Discount"
.Range("H1").Value = InputBox("What percentage") / 100
.Range("H1").NumberFormat = "0.00%"
.Range("H1").Interior.ColorIndex = 6
' Enters the Currency
.Range("B3") = Worksheets("Source Code").Range("A4")
.Range("C3") = Worksheets("Source Code").Range("B4")
' Enters the # of Users
.Range("B4") = "Users"
.Range("C4") = Worksheets("Source Code").Range("B5")
' Enters the Platform Type
.Range("B5") = "Platform/Edition"
.Range("C5") = Worksheets("Source Code").Range("A12")
.Range("D5") = Worksheets("Source Code").Range("B12")
' Enters the Addtl part numbers
Set Target = .Range("B6")
End With
Set rPart = Worksheets("Source Code").Range("B17")
Do Until rPart = ""
Target.Offset(, 1) = rPart.Offset(, -1).Value
Target.Offset(, 2) = rPart.Offset(, 1)
Set Target = Target.Offset(1)
Set rPart = rPart.Offset(1)
Loop

' Enters the Grand Total Price
Target.Offset(, 0) = "Term Model Price"
Target.Offset(, 2) = Worksheets("Source Code").Range("TermModel.Price")
Set Target = Target.Offset(1)
Set rPart = rPart.Offset(1)

' Enters the SW tools
Target.Offset(, 0) = "SW Tools"
Target.Offset(, 2) = Worksheets("SW Tools").Range("B14")



'Application.DisplayAlerts = False
'ws.Delete
'Application.DisplayAlerts = True
'Set rPart = Worksheets("Source Code").Range("B17")


' Start Word Doc
' ws.Activate
' DumptoWord1


End Sub


Rick Rothstein said:
First off, you are putting the value 10 into the cell *first* and then
telling Excel to make that into a percentage... 10, as a percentage is
1000%. If your users will be typing in the actual percentage number, then
you will need to divide it by 100 to make it a correct value. You will have
to build the appropriate controlling code around this, but here is how to
ask for the percentage, insert it into the cell and then color the cell
yellow...

Range("H1").Value = InputBox("What percentage") / 100
Range("H1").NumberFormat = "0.00%"
Range("H1").Interior.ColorIndex = 6

--
Rick (MVP - Excel)


Hi Rick, thank you for helping :> I would like to format the cell [H1] so
that it's yellow with a percentage in it that can be multiplied by the
values
in the rest of the worksheet

so for example
Input box asks what the discount is: They type in 10, but for some reason
it's then returning 1000% and I am hoping it'll place a 10% in there and
the
background show up in Yellow

:

Explain what you are looking for when you say "highlighting the cell"...
exactly what do you have in mind here?

--
Rick (MVP - Excel)


currently I only know how to get it to be in discount form? I don't
know
how
to add to the format to include highlighting the cell?

:

Selecting the cell doesn't "highlight" it enough for you? If not, then
you
need to tell us what or how your want the highlight to look. As for
asking
the user for the discount amount, you can use an InputBox. For
example,
perhaps like this...

..Range("H1").Value = InputBox("Discount amount?")

--
Rick (MVP - Excel)


is there a way to highlight this cell as well and ask the user for
how
much
they would like to discount?
' Formats the Discount Percentage
.Range("H1").Select
Selection.NumberFormat = "0.00%"

Thank you!
Click to expand...
Click to expand...
Click to expand...
 
also, I would suggest that you add some error checking after your inputbox
to enure numeric data only has been entered & to handle the cancel button
being pressed.
Click to expand...

You could also just wrap the InputBox function with the Val function
(assuming US decimal entries, of course)...

Range("H1").Value = Val(InputBox("Discount amount?")) / 100
 
You have been a really big help!! I am so appreciative -- thank you!!!! :> I
hope someday this comes easier to me.

Is there a way for me to now apply the discount to all the numbers in Column
D and place them in Column E?

Then I'm done till I have to get it into word :)


Rick Rothstein said:
Are you saying that your SourceCode worksheet is being changed? A fast
reading of your code makes that seem unlikely... I don't see any code that
directly changes anything on the SourceCode sheet. Well, perhaps one thing;
this code...

Columns("B:B").ColumnWidth = 20
Columns("C:C").ColumnWidth = 30

will change the column width on the active worksheet because it does not
have a dot in front of the Columns property... if you put a dot in front of
them, then they will refer back to the WS sheet; otherwise, all the rest of
your code appears to be changing only the TermSummary sheet.

I do want to point out one thing about your code structure - these lines...

..Range("D:D").Select
Selection.NumberFormat = "#,##0_);[Red](#,##0)"

can be rewritten like this...

..Range("D:D").NumberFormat = "#,##0_);[Red](#,##0)"

Perhaps this previous posting of mine (a response to another person using
Select/Selection type constructions) will be of some help to you in your
future programming...

Whenever you see code constructed like this...

Range("A1").Select
Selection.<whatever>

you can almost always do this instead...

Range("A1").<whatever>

In your particular case, you have this...

Range("C2:C8193").Select 'select cells to export
For Each r In Selection.Rows

which, using the above concept, can be reduced to this...

For Each r In Range("C2:C8193").Rows

Notice, all I have done is replace Selection with the range you Select(ed)
in the previous statement and eliminate the process of doing any
Select(ion)s. Stated another way, the Selection produced from
Range(...).Select is a range and, of course, Range(...) is a range... and,
in fact, they are the same range, so it doesn't matter which one you use.
The added benefit of not selecting ranges first is your active cell does not
change.


--
Rick (MVP - Excel)


Heather said:
That works awesome -- thank you! One last question

Now that I have this working for this tab I noticed that when I hit the
macro button in another tab it's formatting that tab on not this new tab?
How do I add the right coding to this so that it formats the [TermSummary]
tab not the [SourceCode] tab?

Thanks again -- this is a huge help!!! :>
Sub CreateTerm()
Dim ws As Worksheet
Set ws = gettab("TermSummary")
Dim rPart As Range
Dim Target As Range
With ws

' Formats the Column widths
Columns("B:B").ColumnWidth = 20
Columns("C:C").ColumnWidth = 30
' Formats the Currency
.Range("D:D").Select
Selection.NumberFormat = "#,##0_);[Red](#,##0)"
' Enters the Discount Allocation
.Range("G1") = "Apply Discount"
.Range("H1").Value = InputBox("What percentage") / 100
.Range("H1").NumberFormat = "0.00%"
.Range("H1").Interior.ColorIndex = 6
' Enters the Currency
.Range("B3") = Worksheets("Source Code").Range("A4")
.Range("C3") = Worksheets("Source Code").Range("B4")
' Enters the # of Users
.Range("B4") = "Users"
.Range("C4") = Worksheets("Source Code").Range("B5")
' Enters the Platform Type
.Range("B5") = "Platform/Edition"
.Range("C5") = Worksheets("Source Code").Range("A12")
.Range("D5") = Worksheets("Source Code").Range("B12")
' Enters the Addtl part numbers
Set Target = .Range("B6")
End With
Set rPart = Worksheets("Source Code").Range("B17")
Do Until rPart = ""
Target.Offset(, 1) = rPart.Offset(, -1).Value
Target.Offset(, 2) = rPart.Offset(, 1)
Set Target = Target.Offset(1)
Set rPart = rPart.Offset(1)
Loop

' Enters the Grand Total Price
Target.Offset(, 0) = "Term Model Price"
Target.Offset(, 2) = Worksheets("Source Code").Range("TermModel.Price")
Set Target = Target.Offset(1)
Set rPart = rPart.Offset(1)

' Enters the SW tools
Target.Offset(, 0) = "SW Tools"
Target.Offset(, 2) = Worksheets("SW Tools").Range("B14")



'Application.DisplayAlerts = False
'ws.Delete
'Application.DisplayAlerts = True
'Set rPart = Worksheets("Source Code").Range("B17")


' Start Word Doc
' ws.Activate
' DumptoWord1


End Sub


Rick Rothstein said:
First off, you are putting the value 10 into the cell *first* and then
telling Excel to make that into a percentage... 10, as a percentage is
1000%. If your users will be typing in the actual percentage number, then
you will need to divide it by 100 to make it a correct value. You will
have
to build the appropriate controlling code around this, but here is how to
ask for the percentage, insert it into the cell and then color the cell
yellow...

Range("H1").Value = InputBox("What percentage") / 100
Range("H1").NumberFormat = "0.00%"
Range("H1").Interior.ColorIndex = 6

--
Rick (MVP - Excel)


Hi Rick, thank you for helping :> I would like to format the cell [H1]
so
that it's yellow with a percentage in it that can be multiplied by the
values
in the rest of the worksheet

so for example
Input box asks what the discount is: They type in 10, but for some
reason
it's then returning 1000% and I am hoping it'll place a 10% in there
and
the
background show up in Yellow

:

Explain what you are looking for when you say "highlighting the
cell"...
exactly what do you have in mind here?

--
Rick (MVP - Excel)


currently I only know how to get it to be in discount form? I don't
know
how
to add to the format to include highlighting the cell?

:

Selecting the cell doesn't "highlight" it enough for you? If not,
then
you
need to tell us what or how your want the highlight to look. As for
asking
the user for the discount amount, you can use an InputBox. For
example,
perhaps like this...

..Range("H1").Value = InputBox("Discount amount?")

--
Rick (MVP - Excel)


is there a way to highlight this cell as well and ask the user
for
how
much
they would like to discount?
' Formats the Discount Percentage
.Range("H1").Select
Selection.NumberFormat = "0.00%"

Thank you!
Click to expand...
Click to expand...
Click to expand...
 
Just loop through the values in Column D and multiply their values by the
contents of H1. Generally, something like this (don't forget to Dim your
variables)...

' This code assumes it is inside a
' With/End With block; hence, the leading dots
LastRow = .Cells(ws.Rows.Count, "D").End(xlup).Row
For X = 1 to LastRow
.Cells(X, "E").Value = .Range("H1") * .Cells(X, "D").Value
Next

--
Rick (MVP - Excel)


Heather said:
You have been a really big help!! I am so appreciative -- thank you!!!! :>
I
hope someday this comes easier to me.

Is there a way for me to now apply the discount to all the numbers in
Column
D and place them in Column E?

Then I'm done till I have to get it into word :)


Rick Rothstein said:
Are you saying that your SourceCode worksheet is being changed? A fast
reading of your code makes that seem unlikely... I don't see any code
that
directly changes anything on the SourceCode sheet. Well, perhaps one
thing;
this code...

Columns("B:B").ColumnWidth = 20
Columns("C:C").ColumnWidth = 30

will change the column width on the active worksheet because it does not
have a dot in front of the Columns property... if you put a dot in front
of
them, then they will refer back to the WS sheet; otherwise, all the rest
of
your code appears to be changing only the TermSummary sheet.

I do want to point out one thing about your code structure - these
lines...

..Range("D:D").Select
Selection.NumberFormat = "#,##0_);[Red](#,##0)"

can be rewritten like this...

..Range("D:D").NumberFormat = "#,##0_);[Red](#,##0)"

Perhaps this previous posting of mine (a response to another person using
Select/Selection type constructions) will be of some help to you in your
future programming...

Whenever you see code constructed like this...

Range("A1").Select
Selection.<whatever>

you can almost always do this instead...

Range("A1").<whatever>

In your particular case, you have this...

Range("C2:C8193").Select 'select cells to export
For Each r In Selection.Rows

which, using the above concept, can be reduced to this...

For Each r In Range("C2:C8193").Rows

Notice, all I have done is replace Selection with the range you
Select(ed)
in the previous statement and eliminate the process of doing any
Select(ion)s. Stated another way, the Selection produced from
Range(...).Select is a range and, of course, Range(...) is a range...
and,
in fact, they are the same range, so it doesn't matter which one you use.
The added benefit of not selecting ranges first is your active cell does
not
change.


--
Rick (MVP - Excel)


Heather said:
That works awesome -- thank you! One last question

Now that I have this working for this tab I noticed that when I hit the
macro button in another tab it's formatting that tab on not this new
tab?
How do I add the right coding to this so that it formats the
[TermSummary]
tab not the [SourceCode] tab?

Thanks again -- this is a huge help!!! :>
Sub CreateTerm()
Dim ws As Worksheet
Set ws = gettab("TermSummary")
Dim rPart As Range
Dim Target As Range
With ws

' Formats the Column widths
Columns("B:B").ColumnWidth = 20
Columns("C:C").ColumnWidth = 30
' Formats the Currency
.Range("D:D").Select
Selection.NumberFormat = "#,##0_);[Red](#,##0)"
' Enters the Discount Allocation
.Range("G1") = "Apply Discount"
.Range("H1").Value = InputBox("What percentage") / 100
.Range("H1").NumberFormat = "0.00%"
.Range("H1").Interior.ColorIndex = 6
' Enters the Currency
.Range("B3") = Worksheets("Source Code").Range("A4")
.Range("C3") = Worksheets("Source Code").Range("B4")
' Enters the # of Users
.Range("B4") = "Users"
.Range("C4") = Worksheets("Source Code").Range("B5")
' Enters the Platform Type
.Range("B5") = "Platform/Edition"
.Range("C5") = Worksheets("Source Code").Range("A12")
.Range("D5") = Worksheets("Source Code").Range("B12")
' Enters the Addtl part numbers
Set Target = .Range("B6")
End With
Set rPart = Worksheets("Source Code").Range("B17")
Do Until rPart = ""
Target.Offset(, 1) = rPart.Offset(, -1).Value
Target.Offset(, 2) = rPart.Offset(, 1)
Set Target = Target.Offset(1)
Set rPart = rPart.Offset(1)
Loop

' Enters the Grand Total Price
Target.Offset(, 0) = "Term Model Price"
Target.Offset(, 2) = Worksheets("Source
Code").Range("TermModel.Price")
Set Target = Target.Offset(1)
Set rPart = rPart.Offset(1)

' Enters the SW tools
Target.Offset(, 0) = "SW Tools"
Target.Offset(, 2) = Worksheets("SW Tools").Range("B14")



'Application.DisplayAlerts = False
'ws.Delete
'Application.DisplayAlerts = True
'Set rPart = Worksheets("Source Code").Range("B17")


' Start Word Doc
' ws.Activate
' DumptoWord1


End Sub


:

First off, you are putting the value 10 into the cell *first* and then
telling Excel to make that into a percentage... 10, as a percentage is
1000%. If your users will be typing in the actual percentage number,
then
you will need to divide it by 100 to make it a correct value. You will
have
to build the appropriate controlling code around this, but here is how
to
ask for the percentage, insert it into the cell and then color the
cell
yellow...

Range("H1").Value = InputBox("What percentage") / 100
Range("H1").NumberFormat = "0.00%"
Range("H1").Interior.ColorIndex = 6

--
Rick (MVP - Excel)


Hi Rick, thank you for helping :> I would like to format the cell
[H1]
so
that it's yellow with a percentage in it that can be multiplied by
the
values
in the rest of the worksheet

so for example
Input box asks what the discount is: They type in 10, but for some
reason
it's then returning 1000% and I am hoping it'll place a 10% in there
and
the
background show up in Yellow

:

Explain what you are looking for when you say "highlighting the
cell"...
exactly what do you have in mind here?

--
Rick (MVP - Excel)


currently I only know how to get it to be in discount form? I
don't
know
how
to add to the format to include highlighting the cell?

:

Selecting the cell doesn't "highlight" it enough for you? If
not,
then
you
need to tell us what or how your want the highlight to look. As
for
asking
the user for the discount amount, you can use an InputBox. For
example,
perhaps like this...

..Range("H1").Value = InputBox("Discount amount?")

--
Rick (MVP - Excel)


is there a way to highlight this cell as well and ask the user
for
how
much
they would like to discount?
' Formats the Discount Percentage
.Range("H1").Select
Selection.NumberFormat = "0.00%"

Thank you!
Click to expand...
Click to expand...
Click to expand...
 
Back
Top