Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
What approach should I adopt ..... | Excel Discussion (Misc queries) | |||
Help with a formula. Lookup? referance? | Excel Worksheet Functions | |||
Circular Referance | Excel Discussion (Misc queries) | |||
How to referance a GroupName | Excel Programming | |||
Cross Referance | Excel Programming |