Conditional Formatting More Than 3 Items

  • Thread starter Thread starter stevedemo77
  • Start date Start date
S

stevedemo77

I have a worksheet with data in A27:O250, and I need to color code each
individual row based on the value in column C of that row. There will be at
least 6-8 different values in column C from which I need to base the row
color from, so conditional formatting won't work. Any help is greatly
appreciated!

Thanks
 
Can you give us a hint what those 6-8 different values might be (or look
like)? And are you saying you will not know in advance how many different
values there might be? If so, is there a maximum number of different values
possible? Also, I'm guessing you will want a different color for each
different value, right?
 
Yes, the values are: ACT, BLD, BUD, CV, CVA, IRL, REV, and each one would
have a different color, and if it were any other value there would be no
color needed. This should be the entire list of possible values. Thanks!
 
Right-click on the tab of the Sheet that you need the code for. paste this
into the window that opens:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Num As Long
Dim rng As Range
Dim vRngInput As Range
Set vRngInput = Intersect(Target, Range("A:Z")) '<---------change to suit
If vRngInput Is Nothing Then Exit Sub
On Error GoTo endit
Application.EnableEvents = False
For Each rng In vRngInput
'Determine the color
Select Case UCase(rng.Value)
Case Is = "ACT": Num = 10 'green
Case Is = "BLD": Num = 2 'black
Case Is = "BUD": Num = 5 'blue
Case Is = "CV": Num = 7 'magenta
Case Is = "CVA": Num = 46 'orange
Case Is = "IRL": Num = 3 'red
Case Is = "REV": Num = 4 '
End Select
'Apply the color
rng.Interior.ColorIndex = Num
Next rng
endit:
Application.EnableEvents = True
End Sub

Here is a list of excel colors:
http://www.mvps.org/dmcritchie/excel/colors.htm

HTH,
Ryan---
 
Is there something else I need to do besides change the range? This doesn't
appear to do anything.
 
The code Ryan posted works automatically (similar to how Conditional
Formatting would have)... type one of your codes into a cell within the
range you changed the example Range("A:Z") to and it should become colored
in (with the exception maybe of BLD since Black, on my system is showing as
white).
 
Paste that code behind the sheet (right-click the sheet and paste into the
window that opens). Then go back to the sheet and type ACT into Cell A1.
What happens?

HTH,
Ryan---
 
Back
Top