Conditional Formatting More Than 3 Items

[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

 

[Rick Rothstein]

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?

 

[stevedemo77]

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!

 

[ryguy7272]

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

 

[stevedemo77]

Is there something else I need to do besides change the range? This doesn't
appear to do anything.

 

[Rick Rothstein]

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

 

[stevedemo77]

Strange...still not working. I must be missing something.

 

[ryguy7272]

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