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

[J_J]

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 = _

"IIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIII
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 = _

"IIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIII
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

 

[Nigel]

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

Sub blue()
ActiveCell.FormulaR1C1 = _
"IIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIII
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 = _
"IIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIII
IIIIIIIIIIIIIIIIIIIIIIIII"
With ActiveCell.Characters(Sheets("Sheet1").Range("H1").Value + 1, 100).Font
.Name = "Arial"
.FontStyle = "Normal"
.Size = 10
.ColorIndex = 1
End With
End Sub

 

[J_J]

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

 

[J_J]

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

 

[Nigel]

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 =
"IIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIII
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

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

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

Sub blue()
ActiveCell.FormulaR1C1 = _

"IIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIII

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 = _

"IIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIII

 

[J_J]

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

 

[J_J]

Hi Nigel,
Yes...this was it!
Regards
J_J

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 =
"IIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIII
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

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

"IIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIII"IIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIII