vba for color coding

  • Thread starter Thread starter desperate-from-seattle
  • Start date Start date
D

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