Mimicing Conditional Formating

  • Thread starter Thread starter Roger R
  • Start date Start date
R

Roger R

I posted this problem a few days ago: I'm trying to attach
a color format to a cell depending on it's value, I also
want to be able to remove the value, but have the color
remain. I recieved help in this form:


Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("Q9:Q512")) Is
Nothing Then
With Target
Select Case .Value
Case Is >= 11: .Interior.ColorIndex =
43 'yellow
Case Is = 0: .Interior.ColorIndex = 3 'red
Case Is <= 10: .Interior.ColorIndex =
36 'green
Case Else: .Interior.ColorIndex = 2 'white
End Select
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

Which works well, although when I paste in to the
worksheet with this code it does not allow me to paste
more than one cell at a time. Meaning, if I paste more
than one cell at a time the color blocking does not work.

I tried to do a simple formula which seemed to work, but
it did not switch colors depending on the value of the
cell, it simply used the color from the cell where the
original formula was copied.

I hope you understand what I'm looking for. If not feel
free to use my personal email.

Thanks
Roger
 
This will work with all the cells in the designated range:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim rCell As Range
Dim rIntersection
Set rIntersection = Intersect(Target, Range("Q9:Q512"))
If Not rIntersection Is Nothing Then
For Each rCell In rIntersection
With rCell
Select Case .Value
Case Is >= 11: .Interior.ColorIndex = 43 'yellow
Case Is = 0: .Interior.ColorIndex = 3 'red
Case Is <= 10: .Interior.ColorIndex = 36 'green
Case Else: .Interior.ColorIndex = 2 'white
End Select
End With
Next rCell
End If
End Sub

Note that negative values will be colored the same as <=10, and the Case
Else will execute only if the value is between 10 and 11, exclusive. Not
sure if that's what you want. I'd have thought perhaps this would be
more logical, but I'm not sure what you're trying to accomplish (nor,
for that matter, why you can't use conditional formatting - it's far
more efficient):

Select Case .Value
Case Is > 10: .Interior.ColorIndex = 43 'green
Case Is > 0: .Interior.ColorIndex = 36 'yellow
Case 0: .Interior.ColorIndex = 3 'red
Case Else: .Interior.ColorIndex = 2 'white
End Select

Note also that on my system 43 is the default colorindex for "lime", not
yellow, while 36 is the default colorindex for "light yellow", not
green. You may have them switched.
 
Back
Top