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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Back
Top