The attachment has three worksheets: Test, CFControl, and ColorPalette
ColorPalette contains the 56 codes and RGB values for each (mainly for reference)
CFControl contains the reference for the code. The code essentially looks at Column A, then returns the value in Column B to determine color used. There is a dynamic named range: rngColors
=OFFSET(CFControl!$A$2,0,0,COUNTA(CFControl!$A:A)-1,2)
The Code actually goes into the worksheet Test (right click the worksheet name and choose "View code", which is what you will see here. It includes the range B3:F11, which you can change as appropriate
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
' Conditional Formatting for more than 3 conditions
Dim rng As Range
' Target is a range::therefore,it can be more than one cell
' For example,,someone could delete the contents of a range,
' or someone could enter an array..
Set rng = Intersect(Target, Range("B3:F11"))
If rng Is Nothing Then
Exit Sub
Else
Dim cl As Range
For Each cl In rng
On Error Resume Next
' — The preceding line doesn ’t change the cell ’s background
' — color if the cell ’s value is not found in the range
' — that we specified ((rngcolors).
cl.Interior.ColorIndex = _
Application.WorksheetFunction.VLookup(cl.Value, _
ThisWorkbook.Sheets("CFControl").Range("rngColors"), 2, False)
If Err.Number <> 0 Then
cl.Interior.ColorIndex = xlNone
End If
Next cl
End If
End Sub
Now, as you change a value in the grid on the Test worksheet, the color will automatically change.
EDIT; Just remembered where I picked this up:
VBA and Macros for MS Excel by Bill Jelen, Tracy Syrstad, et al (from MrExcel.com), pages 297-298.
dBforums
>
Finding the number of colors on the color pallette
07-19-06, 03:01
Finding the number of colors on the color pallette
Linear Mode
