vba for color coding
 

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?)

vba for color coding
 

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

On Thu, 6 May 2010 16:09:01 -0700, desperate-from-seattle
wrote:

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?)