Query Source : Excel Macros Google Group
Solution Type : VBA Macro
Query by : ExcelUser777
Solution by : Ashish Jain (MCAS; MCA; Lead Trainer, Success Electrons)
Query / Problem:
Basically i'd like to only show coloring in part of a cell, so for example
Cell A1 is grey only 33% of the cell is grey -I'd like to color one cell grey 33%. I have several cells to color, different percentages. Once I see one macro I can create the other percentages. Is that possible with a macro?
Appreciate all your help.
Excel 2003 preferably
1. Using Macro
i) Select the range (It should be in terms of percentage, else change the following code accordingly.)
ii) Press Alt+F11.
iii) Paste the following code in Code window.
iv) Run the macro and enjoy.
Dim myCell As Range
For Each myCell In Selection.Cells
'Set TextBox Left Positionx = myCell.Left + 1
'Set TextBox Top Postiony = myCell.Top + 1
'Set TextBox Widthwidth_ = myCell.Value * 58
'Set TextBox Valuetext_ = myCell.Value * 100 & "%"
'Add TextBoxActiveSheet.Shapes.AddTextbox(msoTextOrientationHorizontal, x, y, width_, 10).Select
'Add Text to TextboxSelection.Characters.Text = text_
'Fill Textbox with Gray ColorSelection.ShapeRange.Fill.ForeColor.SchemeColor = 22
Selection.ShapeRange.Line.ForeColor.SchemeColor = 22
'Change FontWith Selection.Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 8
'Align Text in TextboxWith Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
'Clear contents of CellmyCell.Clear
2. Using REPT Function
i) Use REPT function to repeat the pipe symbol, | as displayed in formula box Below.