Multiple Conditional Formatting Help - VBA

[jacksparrow]

Hello all,

I have columns of numbers like so:

A B C D E F
​

1
2
3


I want to setup some code so that the values in D E F would determine the color for the corresponding values in A B C. So if D1 < 10, then A1 = green, if D1 is between 10 and 20, then A1 = red and so on. E1 would likewise determine B1; F3 would determine C3, etc.

I have this code, but I don't know how to alter it to meet the conditions above. Any help would be appreciated.

Private Sub Worksheet_Change(ByVal Target As Range)

Dim icolor As Integer

If Not Intersect(Target, Range("A1:A10")) Is Nothing Then

Select Case Target

Case 1 To 5

icolor = 6
​

Case 6 To 10

icolor = 12
​

Case 11 To 15

icolor = 7
​

Case 16 To 20

icolor = 53
​

Case 21 To 25

icolor = 15
​

Case 26 To 30

icolor = 42
​

Case Else

'Whatever
​

End Select
​

Target.Interior.ColorIndex = icolor
​

End If
​

End Sub

 

[Tigs81]

Hi jacksparrow
I am wondering why you are using VBA for this when you can just use Conditional Formatting?
As by the sound of it, you only have 3 outcome for each cell.

Thanks
Tigs

 

[jacksparrow]

I should have been more clear. I want to be able to use multiple colors, maybe up to 20 of them.

 

[Tigs81]

Hi


Change the range in the line

If Not Intersect(Target, Range("A1:A10")) Is Nothing Then

to you requirements.

If Not Intersect(Target, Range("A1:C100")) Is Nothing Then

Change the line

Select Case Target

to

Select Case Target.Offset(0, 3)

use the color palette to determine the color code that you want to use
http://dmcritchie.mvps.org/excel/colors.htm

bearing in mind this will only work when you manually change the cell contents in column a b or c not if they are formulas

Thanks
Tigs