Cell Progress Indicator

Submitted by Tom Urtis.

This example builds a progress indicator in Column C based on entries in Columns A and B (see Figure 13.12):

Figure 13.12

Cell progress indicator.

Figure 13.12

Cell progress indicator.

Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column > 2 Or Target.Cells.Count > 1 Then Exit Sub If Application.IsNumber(Target.Value) = False Then Application.EnableEvents = False Application.Undo

Application.EnableEvents = True MsgBox "Numbers only please." Exit Sub End If

Select Case Target.Column Case 1

If Target.Value > Target.Offset(0, 1).Value Then Application.EnableEvents = False Application.Undo Application.EnableEvents = True

MsgBox "Value in column A may not be larger than value in column B." Exit Sub End If Case 2

If Target.Value < Target.Offset(0, -1).Value Then Application.EnableEvents = False Application.Undo Application.EnableEvents = True

MsgBox "Value in column B may not be smaller " & _

"than value in column A." Exit Sub End If End Select Dim x As Long x = Target.Row Dim z As String z = Range("B" & x).Value - Range("A" & x).Value With Range("C" & x)

.Formula = "=IF(RC[-1]<=RC[-2],REPT(""n"",RC[-1]) _ &REPT(""n"",RC[-2]-RC[-1]),REPT(""n"",RC[-2]) _ &REPT(""o"",RC[-1]-RC[-2]))" .Value = .Value .Font.Name = "Wingdings" .Font.ColorIndex = 1 .Font.Size = 10

If Len(Range("A" & x)) <> 0 Then

.Characters(1, (.Characters.Count - z)).Font.ColorIndex = 3 .Characters(1, (.Characters.Count - z)).Font.Size = 12 End If End With End Sub

0 0

Post a comment