different cellcolor depending on cellvalue

D

dsg

I would like to set the color of the different cells depending on
what value that is in the cell.

Let's say, it should search in the range ("A1:H40") and
depending on what number it is in each field it get different
cellcolor.
ex. if the cell A5 >= 50 it should get a blue background.



Any suggestions ?
All this is going to run inside a macro
 
P

Peter

Hi

here's one way:

Sub populate()
Range("a1").Select
For i = 0 To 7
For j = 0 To 39
ActiveCell.Offset(j, i) = Int((15 * Rnd) + 1)
Next j
Next i


For i = 0 To 7
For j = 0 To 39
Select Case ActiveCell.Offset(j, i).Value
Case 1 To 5
ActiveCell.Offset(j, i).Font.ColorIndex = 3
Case 6 To 10
ActiveCell.Offset(j, i).Font.ColorIndex = 5
Case 11 To 15
ActiveCell.Offset(j, i).Font.ColorIndex = 1
End Select
Next j
Next i


if you copy and paste this into the VB for sheet1 in a new
worksheet it should work. The first thing it does is
populate the range you mentioned (A1:H40) with random
numbers. Then it analyses each number and assigns it a
different colour depending on it value as defined in the
case loop. Hope it helps.

Peter
End Sub
 
Top