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?

--
Rick (MVP - Excel)


"stevedemo77" <(E-Mail Removed)> wrote in message
news:A6E146A9-5484-4902-8929-(E-Mail Removed)...
>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

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!

"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" <(E-Mail Removed)> wrote in message
> news:A6E146A9-5484-4902-8929-(E-Mail Removed)...
> >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
>
>

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 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!
>
> "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" <(E-Mail Removed)> wrote in message
> > news:A6E146A9-5484-4902-8929-(E-Mail Removed)...
> > >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
> >
> >

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 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!
> >
> > "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" <(E-Mail Removed)> wrote in message
> > > news:A6E146A9-5484-4902-8929-(E-Mail Removed)...
> > > >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
> > >
> > >

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" <(E-Mail Removed)> wrote in message
news:22A59D1F-320E-487B-9988-(E-Mail Removed)...
> 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 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!
>> >
>> > "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" <(E-Mail Removed)> wrote in
>> > > message
>> > > news:A6E146A9-5484-4902-8929-(E-Mail Removed)...
>> > > >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
>> > >
>> > >

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" <(E-Mail Removed)> wrote in message
> news:22A59D1F-320E-487B-9988-(E-Mail Removed)...
> > 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 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!
> >> >
> >> > "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" <(E-Mail Removed)> wrote in
> >> > > message
> >> > > news:A6E146A9-5484-4902-8929-(E-Mail Removed)...
> >> > > >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
> >> > >
> >> > >
>
>

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" <(E-Mail Removed)> wrote in message
> > news:22A59D1F-320E-487B-9988-(E-Mail Removed)...
> > > 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 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!
> > >> >
> > >> > "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" <(E-Mail Removed)> wrote in
> > >> > > message
> > >> > > news:A6E146A9-5484-4902-8929-(E-Mail Removed)...
> > >> > > >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
> > >> > >
> > >> > >
> >
> >