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

  • Thread starter Thread starter J_J
  • Start date Start date
J

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
 
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
 
Thanks Nigel,
I am away from my PC now.
I'll try your suggestion and get back to here ASAP.
Regards
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
 
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



J_J said:
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 said:
Jack, Try this for size......

Sub blue()
ActiveCell.FormulaR1C1 = _
Click to expand...
"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 = _
Click to expand...
"IIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIII
Click to expand...
 
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
 
Hi Nigel,
Yes...this was it!
Regards
J_J

Nigel said:
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



J_J said:
Nigel that worked perfectly OK.
I was just wondering...
Can we also assign a variable to the referance cell position so that
Click to expand...
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
Click to expand...
"IIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIII"IIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIII
Click to expand...
 
Back
Top