ExcelBanter - can I adopt this persentage code to use H1 as referance value?

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - can I adopt this persentage code to use H1 as referance value? (https://www.excelbanter.com/excel-programming/319804-can-i-adopt-persentage-code-use-h1-referance-value.html)

can I adopt this persentage code to use H1 as referance value?

Hi,
The below code displays a percentage like bar on the screen
regarding to the numerical values in the sub's. I need to use a
variable for this (preferably taking the cell H1 value on Sheet1).
Is this possible?

'==========================
Sub blue()
ActiveCell.FormulaR1C1 = _

"IIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIII IIIIIIIIIIIIIIIIIIIIIIIIII
IIIIIIIIIIIIIIIIIIIIIIIII"
With ActiveCell.Characters(Start:=1, Length:=20).Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = 5
End With
End Sub

Sub normal()
ActiveCell.FormulaR1C1 = _

"IIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIII IIIIIIIIIIIIIIIIIIIIIIIIII
IIIIIIIIIIIIIIIIIIIIIIIII"
With ActiveCell.Characters(Start:=21, Length:=90).Font
.Name = "Arial"
.FontStyle = "Normal"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = 1
End With
End Sub
'-----------------------------------------------
Private Sub CommandButton1_Click()
blue
normal
End Sub
'=======================

TIA
J_J

can I adopt this persentage code to use H1 as referance value?

Jack, Try this for size......

Sub blue()
ActiveCell.FormulaR1C1 = _
"IIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIII IIIIIIIIIIIIIIIIIIIIIIIIII
IIIIIIIIIIIIIIIIIIIIIIIII"
With ActiveCell.Characters(1, Sheets("Sheet1").Range("H1").Value).Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 10
.ColorIndex = 5
End With
End Sub

Sub normal()
ActiveCell.FormulaR1C1 = _
"IIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIII IIIIIIIIIIIIIIIIIIIIIIIIII
IIIIIIIIIIIIIIIIIIIIIIIII"
With ActiveCell.Characters(Sheets("Sheet1").Range("H1") .Value + 1, 100).Font
.Name = "Arial"
.FontStyle = "Normal"
.Size = 10
.ColorIndex = 1
End With
End Sub

--
Cheers
Nigel

"J_J" wrote in message
...
Hi,
The below code displays a percentage like bar on the screen
regarding to the numerical values in the sub's. I need to use a
variable for this (preferably taking the cell H1 value on Sheet1).
Is this possible?

'==========================
Sub blue()
ActiveCell.FormulaR1C1 = _

"IIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIII IIIIIIIIIIIIIIIIIIIIIIIIII
IIIIIIIIIIIIIIIIIIIIIIIII"
With ActiveCell.Characters(Start:=1, Length:=20).Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = 5
End With
End Sub

Sub normal()
ActiveCell.FormulaR1C1 = _

"IIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIII IIIIIIIIIIIIIIIIIIIIIIIIII
IIIIIIIIIIIIIIIIIIIIIIIII"
With ActiveCell.Characters(Start:=21, Length:=90).Font
.Name = "Arial"
.FontStyle = "Normal"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = 1
End With
End Sub
'-----------------------------------------------
Private Sub CommandButton1_Click()
blue
normal
End Sub
'=======================

TIA
J_J

can I adopt this persentage code to use H1 as referance value?

Thanks Nigel,
I am away from my PC now.
I'll try your suggestion and get back to here ASAP.
Regards
J_J

"Nigel" wrote in message
...
Jack, Try this for size......

Sub blue()
ActiveCell.FormulaR1C1 = _

"IIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIII IIIIIIIIIIIIIIIIIIIIIIIIII
IIIIIIIIIIIIIIIIIIIIIIIII"
With ActiveCell.Characters(1, Sheets("Sheet1").Range("H1").Value).Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 10
.ColorIndex = 5
End With
End Sub

Sub normal()
ActiveCell.FormulaR1C1 = _

"IIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIII IIIIIIIIIIIIIIIIIIIIIIIIII
IIIIIIIIIIIIIIIIIIIIIIIII"
With ActiveCell.Characters(Sheets("Sheet1").Range("H1") .Value + 1,
100).Font
.Name = "Arial"
.FontStyle = "Normal"
.Size = 10
.ColorIndex = 1
End With
End Sub

--
Cheers
Nigel

"J_J" wrote in message
...
Hi,
The below code displays a percentage like bar on the screen
regarding to the numerical values in the sub's. I need to use a
variable for this (preferably taking the cell H1 value on Sheet1).
Is this possible?

'==========================
Sub blue()
ActiveCell.FormulaR1C1 = _

"IIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIII IIIIIIIIIIIIIIIIIIIIIIIIII
IIIIIIIIIIIIIIIIIIIIIIIII"
With ActiveCell.Characters(Start:=1, Length:=20).Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = 5
End With
End Sub

Sub normal()
ActiveCell.FormulaR1C1 = _

"IIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIII IIIIIIIIIIIIIIIIIIIIIIIIII
IIIIIIIIIIIIIIIIIIIIIIIII"
With ActiveCell.Characters(Start:=21, Length:=90).Font
.Name = "Arial"
.FontStyle = "Normal"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = 1
End With
End Sub
'-----------------------------------------------
Private Sub CommandButton1_Click()
blue
normal
End Sub
'=======================

TIA
J_J

can I adopt this persentage code to use H1 as referance value?

Sorry Nigel
Possibly I've asked too many questions on the eve of the new year...:)
happy new year to you and to all NG members.
J_J

is auto incrementing possible too?

Hi J_J
Here is a simplication that will put a bar in cells A1 to A10 based on the
values you enter in cells H1 to H10.
As you change cells H1 to H10 the bar will change accordingly. Hope this
helps.

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Column = 8 And Target.Row <= 10 Then
With Cells(Target.Row, 1)
.Value =
"IIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIII IIIIIIIIIIIIIIIIIIIIIIIIII
IIIIIIIIIIIIIIIIIIIIIIIII"
With .Font
.Name = "Arial"
.Size = 10
.FontStyle = "Bold"
End With
With .Characters(1, Target.Value).Font
.ColorIndex = 5
End With
With .Characters(Target.Value + 1, 100).Font
.ColorIndex = 1
End With
End With
End If
End Sub

--
Cheers
Nigel

"J_J" wrote in message
...
Nigel that worked perfectly OK.
I was just wondering...
Can we also assign a variable to the referance cell position so that when
we
increment the cell position in a loopy manner the plotted persentage like
figures (displayed in cells A1, A2,....,A10) will reflect values which are
present in H1, H2,.....,H20 respectively?. I hope I maneged to ask the
question properly.
J_J

"Nigel" wrote in message
...
Jack, Try this for size......

Sub blue()
ActiveCell.FormulaR1C1 = _

"IIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIII IIIIIIIIIIIIIIIIIIIIIIIIII
IIIIIIIIIIIIIIIIIIIIIIIII"
With ActiveCell.Characters(1,
Sheets("Sheet1").Range("H1").Value).Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 10
.ColorIndex = 5
End With
End Sub

Sub normal()
ActiveCell.FormulaR1C1 = _

"IIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIII IIIIIIIIIIIIIIIIIIIIIIIIII
IIIIIIIIIIIIIIIIIIIIIIIII"
With ActiveCell.Characters(Sheets("Sheet1").Range("H1") .Value + 1,
100).Font
.Name = "Arial"
.FontStyle = "Normal"
.Size = 10
.ColorIndex = 1
End With
End Sub

--
Cheers
Nigel

is auto incrementing possible too?

Nigel that worked perfectly OK.
I was just wondering...
Can we also assign a variable to the referance cell position so that when we
increment the cell position in a loopy manner the plotted persentage like
figures (displayed in cells A1, A2,....,A10) will reflect values which are
present in H1, H2,.....,H20 respectively?. I hope I maneged to ask the
question properly.
J_J

"Nigel" wrote in message
...
Jack, Try this for size......

Sub blue()
ActiveCell.FormulaR1C1 = _

"IIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIII IIIIIIIIIIIIIIIIIIIIIIIIII
IIIIIIIIIIIIIIIIIIIIIIIII"
With ActiveCell.Characters(1, Sheets("Sheet1").Range("H1").Value).Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 10
.ColorIndex = 5
End With
End Sub

Sub normal()
ActiveCell.FormulaR1C1 = _

"IIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIII IIIIIIIIIIIIIIIIIIIIIIIIII
IIIIIIIIIIIIIIIIIIIIIIIII"
With ActiveCell.Characters(Sheets("Sheet1").Range("H1") .Value + 1,
100).Font
.Name = "Arial"
.FontStyle = "Normal"
.Size = 10
.ColorIndex = 1
End With
End Sub

--
Cheers
Nigel