Conditional Formatting More Than 3 Items
 

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

Conditional Formatting More Than 3 Items
 

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?

--
Rick (MVP - Excel)


"stevedemo77" wrote in message
...
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

Conditional Formatting More Than 3 Items
 

Yes, the values a 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!

"Rick Rothstein" wrote:

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?

--
Rick (MVP - Excel)


"stevedemo77" wrote in message
...
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

Conditional Formatting More Than 3 Items
 

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

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"stevedemo77" wrote:

Yes, the values a 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!

"Rick Rothstein" wrote:

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?

--
Rick (MVP - Excel)


"stevedemo77" wrote in message
...
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

Conditional Formatting More Than 3 Items
 

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

"ryguy7272" wrote:

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

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"stevedemo77" wrote:

Yes, the values a 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!

"Rick Rothstein" wrote:

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?

--
Rick (MVP - Excel)


"stevedemo77" wrote in message
...
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

Conditional Formatting More Than 3 Items
 

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

--
Rick (MVP - Excel)


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

"ryguy7272" wrote:

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

--
Ryan---
If this information was helpful, please indicate this by clicking
''Yes''.


"stevedemo77" wrote:

Yes, the values a 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!

"Rick Rothstein" wrote:

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?

--
Rick (MVP - Excel)


"stevedemo77" wrote in
message
...
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

Conditional Formatting More Than 3 Items
 

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

"Rick Rothstein" wrote:

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

--
Rick (MVP - Excel)


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

"ryguy7272" wrote:

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

--
Ryan---
If this information was helpful, please indicate this by clicking
''Yes''.


"stevedemo77" wrote:

Yes, the values a 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!

"Rick Rothstein" wrote:

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?

--
Rick (MVP - Excel)


"stevedemo77" wrote in
message
...
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

Conditional Formatting More Than 3 Items
 

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

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"stevedemo77" wrote:

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

"Rick Rothstein" wrote:

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

--
Rick (MVP - Excel)


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

"ryguy7272" wrote:

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

--
Ryan---
If this information was helpful, please indicate this by clicking
''Yes''.


"stevedemo77" wrote:

Yes, the values a 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!

"Rick Rothstein" wrote:

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?

--
Rick (MVP - Excel)


"stevedemo77" wrote in
message
...
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