Conditional Formatting More Than 3 Items

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
 
R

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

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

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

stevedemo77

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

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

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

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

Top