vba for color coding

[desperate-from-seattle]

I love conditional formating but it allows only 3 different colour coding.
I need to set up:
1) 10 different colour coding (possibly using VBA, but dont know how)
2) Functional arguments that cells to determine 10 different specific "text"
from other sheets in the same workbook(using "IF" function?)

 

[Gord Dibben]

1. Use sheet event code.

Private Sub Worksheet_Change(ByVal Target As Range)
Set R = Range("A1:G234") 'adjust range to suit
If Intersect(Target, R) Is Nothing Then
Exit Sub
End If
vals = Array("C", "D", "G", "H", "K", "L", "O", "S", "C", "X")
nums = Array(8, 9, 6, 3, 7, 4, 20, 10, 8, 15)
For Each RR In R
icolor = 0
For i = LBound(vals) To UBound(vals)
If UCase(RR.Value) = vals(i) Then
icolor = nums(i)
End If
Next
If icolor > 0 Then
RR.Interior.ColorIndex = icolor
End If
Next
End Sub

Right-click on the sheet tab and "View Code"

Copy/paste the code into that sheet module.

Adjust range, vals and nums to suit.

2. I don't understand your needs.


Gord Dibben MS Excel MVP