How to Call a user defined input within range function ? or Global Call ?

[Rajxcel]

Hi, First of all Thanks for reading my thread.

So the question is I would like to format a range cells in a certain way. The range of cells (the selected range cannot be more than 9 cells) are given as input by the user. So my code was :

Sub Formatting()
Dim r as range
r = Application.InputBox(prompt := "Enter the Range", type := 8)
range("r").Selection.Number Format = "0.00%;[Red]-0.00%"
End Sub()

I am not sure what is wrong with the code, as there is a prompt to select the cells then the 4th line is not executed. also there is no error during run.

Kindly provide an solution or correct me. Thanks a ton in advance,

[GTO]

Greetings and welcome to VBAX :-)

Try:



Option Explicit

Sub Formatting()
Dim r As Range

'// Skip errors cautiously and test for results right away... //
On Error Resume Next
Set r = Application.InputBox(prompt:="Enter the Range", Type:=8)
On Error GoTo 0

If Not r Is Nothing Then
If r.Cells.Count <= 9 Then
r.NumberFormat = "0.00%;[Red]-0.00%"
Else
MsgBox r.Count & " is too many cells, 9 or less allowed...", vbInformation, vbNullString
Exit Sub
End If
Else
MsgBox "You didn't set a range; now exiting...", vbInformation, vbNullString
Exit Sub
End If

End Sub


Hope that helps,

Mark

[Rajxcel]

Hi GTO, thanks a ton for the answer and the code. It worked like a magic :friends: .

Could you also refer me to some good sources to learn vba, as you might have known I am a newbie and there are so many websites and online courses. I don't want to signup for the wrong one. Seeing that you are guru, I hope you will be able to help me.


Thanks a lot once again for the help.

[snb]

Or use:


Sub M_snb()
Application.InputBox("Select the Range", type := 8).resize(8).NumberFormat = "0.00%;[Red]-0.00%"
End Sub

[Rajxcel]

Or use:


Sub M_snb()
Application.InputBox("Select the Range", type := 8).resize(8).NumberFormat = "0.00%;[Red]-0.00%"
End Sub

super simple and great. thanks a ton. I just had to change the number format as I didn't want them in percentage. Great anyways. Thanks a lot once again.

Can I ask you another question : is there a possibility to run the same formatting macro based on the column header ? Meaning to say in a worksheet out of 10 coloumns I could have 4 coloumns with the heading "Profit" and I want this macro to run on all those coloumns.

Could you please guide me on how I could make the vba read the coloumn heading ?

Thanks a lot :)

[snb]

Please, do not quote....


Sub M_snb()
sn =feuille1.cells(1).currentregion

For j = 1 To UBound(sn, 2)
If sn(1, j) = "Profit" Then c00 = c00 & "," & Feuille1.Cells(1).CurrentRegion.Columns(j).Address
Next

Feuille1.Range(Mid(c00, 2)).NumberFormat = "0.00%;[Red]-0.00%"
End Sub

[Rajxcel]

thanks for the code. super helpful. and sorry for the quote, will learn not to do it from next time. Thanks once again