create an input box to select from and to cell in excel vb

[bhatipuru]

Hello all,

I have a report number of the columns are the same but number of row's changes.

I would like an input box to ask me to enter the beganing cell and ending cell for me to select and use my macro for formatting.

ex:A13 to S27- Where "A13" to "S13" is the starging point and ending point could be A20 to S20 or A14 to S14

 

[VoG]

Dim x as Range
Set x = Application.InputBox("Select range", Type:-8)

 

[bhatipuru]

Dim x as Range
Set x = Application.InputBox("Select range", Type:-8)

Thanks but its not working.

I am getting the input box but unable to select from A13 to s27

 

[Jonmo1]

I think that should be

Type:=8

 

[bhatipuru]

I think that should be

Type:=8

Perfect. Thanks. It works.

 

[bhatipuru]

Perfect. Thanks. It works.

I guess selection did not work. I want to update or change some formatting on the slection.

EX: After selecting "A13:S27", I want to change the borders or formatting for the selected cells only. NOTE: "The selection may different on daily basis"

Set X = Application.InputBox("Select range", Type:=8)
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = 1
.TintAndShade = 0
.Weight = xlMedium
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = 1
.TintAndShade = 0
.Weight = xlMedium
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 1
.TintAndShade = 0
.Weight = xlMedium
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = 1
.TintAndShade = 0
.Weight = xlMedium
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.ColorIndex = 1
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.ColorIndex = 1
.TintAndShade = 0
.Weight = xlThin
End With

 

[Jonmo1]

Change every instance of Selection to X

That's kind of the whole purpose of setting the Variable X to a specific range.
So that you can then refer to X in future code.

 

[Mindpsyche]

Here is something else that is helpful. If you are implementing all borders just use one instance of With like below:

With Selection.Borders
.LineStyle = xlContinuous
.ColorIndex = 1
.TintAndShade = 0
.Weight = xlMedium
End With

Noe after the line:

With Selection.Borders , there is no specific line mentioned. This allows you to implement borders around and inside the range.

 

[bhatipuru]

This tells you "how much I know about excel"

But Thanks it worked......yeeepe

 

[Jonmo1]

Glad to help, thanks for the feedback.

This tells you "how much I know about excel"

No worries, that's what the forum is all about.