Andy,
That should do it-
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
Set rng = Intersect(Target, Range("C24:AF54,C601:AF91,C97:AF127"))
If rng Is Nothing Then
Exit Sub
Else
Dim cl As Range
For Each cl In rng
Select Case cl.Text
Case 1
cl.Interior.ColorIndex = 11
cl.Font.ColorIndex = 11
Case 0.5
cl.Interior.ColorIndex = 41
cl.Font.ColorIndex = 41
Case -1
cl.Interior.ColorIndex = 16
cl.Font.ColorIndex = 16
Case -0.5
cl.Interior.ColorIndex = 15
cl.Font.ColorIndex = 15
Case Else
Selection.Interior.ColorIndex = xlNone
cl.Font.ColorIndex = 0
Exit Sub
End Select
Next cl
End If
End Sub
Mike
"andy" wrote:
> hello Mike,
>
> thanks, works just fine !
> when i delete three values at a time, though, only the format of the first
> cell selected returns to default. can default format can be applied to all
> the selected fields?
>
> thanks again.
> andy
>
> "Mike H" wrote:
>
> > Try this:-
> >
> > Private Sub Worksheet_Change(ByVal Target As Range)
> > Dim rng As Range
> > Set rng = Intersect(Target, Range("C24:AF54,C601:AF91,C97:AF127"))
> > If rng Is Nothing Then
> > Exit Sub
> > Else
> > Dim cl As Range
> > For Each cl In rng
> > Select Case cl.Text
> > Case 1
> > cl.Interior.ColorIndex = 11
> > cl.Font.ColorIndex = 11
> > Case 0.5
> > cl.Interior.ColorIndex = 41
> > cl.Font.ColorIndex = 41
> > Case -1
> > cl.Interior.ColorIndex = 16
> > cl.Font.ColorIndex = 16
> > Case -0.5
> > cl.Interior.ColorIndex = 15
> > cl.Font.ColorIndex = 15
> > Case Else
> > cl.Interior.ColorIndex = xlNone
> > cl.Font.ColorIndex = 0
> > Exit Sub
> > End Select
> > Next cl
> > End If
> > End Sub
> >
> > Mike
> >
> > "andy" wrote:
> >
> > > hello,
> > >
> > > the following code allows me to add more than 3 conditional formats to
> > > fields in the range containing numbers -1, -0.5, 0.5 or 1.
> > >
> > > Private Sub Worksheet_Change(ByVal Target As Range)
> > > Dim rng As Range
> > > Set rng = Intersect(Target, Range("C24:AF54,C601:AF91,C97:AF127"))
> > > If rng Is Nothing Then
> > > Exit Sub
> > > Else
> > > Dim cl As Range
> > > For Each cl In rng
> > > Select Case cl.Text
> > > Case 1
> > > cl.Interior.ColorIndex = 11
> > > cl.Font.ColorIndex = 11
> > > Case 0.5
> > > cl.Interior.ColorIndex = 41
> > > cl.Font.ColorIndex = 41
> > > Case -1
> > > cl.Interior.ColorIndex = 16
> > > cl.Font.ColorIndex = 16
> > > Case -0.5
> > > cl.Interior.ColorIndex = 15
> > > cl.Font.ColorIndex = 15
> > > Case Else
> > > Exit Sub
> > > End Select
> > > Next cl
> > > End If
> > > End Sub
> > >
> > > i have two problems:
> > > - when i delete the values, i would like excel to turn to default formatting
> > > again automatically (i.e. no background and black color text).
> > > - how can i apply the code to values that are already in the range (have
> > > excel update the range automatically when i open the workbook) ?
> > >
> > > as you notice, i'm new to VBA...
> > >
> > > thanks.
> > > andy
|
Similar Threads
