I also need this help and am not sure how to access the VBE in the system, in
addition I am referencing a list of 15 numbers each corresponds to a
different background and need the background to fill an entire row selection
based on the number in the given column. This is for color tracking of
application data as the application works its way through the process each
number corresponds to a step in the process and I want the row of data to
change automatically when I change the number on the row.

Thank you.
--
Judy Rose Cohen


"Cynthia" wrote:

Frank you've been so helpful I'm hoping you can give me one more hint.
Here is the script I have based on your answer below. In my spreadsheet the
column I want to change colors is C: so I changed the range below from what
you had.
In my editor I get a compile error. The 3rd & 4th lines below (If
intersect....then sub) are highlighted in red. Not sure of what I should do
here to get this to work.
What I'm interested in is having the whole column C: (not just a range) be
formatted in this manner.

Can you help one more time.

Thanks,

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count 1 Then Exit Sub
If Intersect(Target, Me.Range("C1:C300")) Is Nothing
Then Exit Sub
On Error GoTo CleanUp
Application.EnableEvents = False
With Target
Select Case .Value
Case "Red": .Interior.ColorIndex = 3
Case "Blue": .Interior.ColorIndex = 10
Case "Green": .Interior.ColorIndex = 4
Case "Yellow": .Interior.ColorIndex = 6
Case "Brown": .Interior.ColorIndex = 9
Case "Black": .Interior.ColorIndex = 1

End Select
End With
CleanUp:
Application.EnableEvents = True

End Sub

"Frank Kabel" wrote:

Hi
The following will color the entry in cell A1:A100 based
on its value:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count 1 Then Exit Sub
If Intersect(Target, Me.Range("A1:A100")) Is Nothing
Then Exit Sub
On Error GoTo CleanUp
Application.EnableEvents = False
With Target
Select Case .Value
Case "Red": .Interior.ColorIndex = 3
Case "Blue": .Interior.ColorIndex = 10
'etc.
End Select
End With
CleanUp:
Application.EnableEvents = True
End Sub


For more about event procedures see:
http://www.cpearson.com/excel/events.htm



--
Regards
Frank Kabel
Frankfurt, Germany

"Cynthia" schrieb im Newsbeitrag
...
Frank,
I don't know VBA programming. I'm new to the user discussion group.
Is there
an area with scripts that I could search?

"Frank Kabel" wrote:

Hi
more conditions are only available if you use VBA. Would this be a
way
for you?.

--
Regards
Frank Kabel
Frankfurt, Germany

"Cynthia" schrieb im
Newsbeitrag
...
I have a spreadsheet where I have set a data validation for the
colum
to
select from a list. The list has names of colors. (Red, yellow,
etc)
A total
of 6 colors. I then set a conditional format for the column to
change
the
background of the cell to the selected color. (The word "red"
displays in
cell and background color of cell is red).

Excel limits the number of conditions to 3. I have 6 color
condtions
(red,
yellow, green, blue, brown, black). How do I get around the 3
limit
condition
where I can set all 6 colors to change when the text is selected
from
the
drop down.

Right-click on the worksheet tab and "View Code"

Copy/paste this code into that module.

Private Sub Worksheet_Change(ByVal Target As Range)
Set r = Range("A:A") 'adjust to suit your range
If Intersect(Target, r) Is Nothing Then
Exit Sub
End If
vals = Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10) 'your 15 numbers will go here
nums = Array(8, 9, 6, 3, 7, 4, 20, 10, 16, 15) 'you need 15 colorindex numbers
For Each rr In r
icolor = 0
For i = LBound(vals) To UBound(vals)
If rr.Value = vals(i) Then
icolor = nums(i)
End If
Next
If icolor 0 Then
rr.EntireRow.Interior.ColorIndex = icolor
End If
Next
End Sub

This code has been tested with 10 numbers in the vals array
The nums array is the colorindex numbers from the Excel color palette

You will have to adjust and edit to suit.

For a list of the colorindex numbers see David McRitchie's site

http://www.mvps.org/dmcritchie/excel/colors.htm

When editing is complete, Alt + q to return to the Excel window.

As always, do all of this on a copy of the worksheet or workbook.


Gord Dibben MS Excel MVP

On Wed, 14 May 2008 09:30:02 -0700, Judy Rose
wrote:

I also need this help and am not sure how to access the VBE in the system, in
addition I am referencing a list of 15 numbers each corresponds to a
different background and need the background to fill an entire row selection
based on the number in the given column. This is for color tracking of
application data as the application works its way through the process each
number corresponds to a step in the process and I want the row of data to
change automatically when I change the number on the row.

Thank you.
--
Judy Rose Cohen

Thank you. Only issue I had was coming up with enough colors different
enough to cover all my categories. Is there a way I could specify if a
certain color fill is used to also use a set pattern or font color? I
understood where your programming came from but wasn't sure where I could
indicate if icolor= (certain value) Then font for entire row = Bold/Italics,
etc.....How to get the program to acknowledge the fact that for a given color
for a row I need the change in font so it will be more visible. If this is
possible would you let me know.

Again, thank you for the help, it worked really well.
--
Judy Rose Cohen


"Gord Dibben" wrote:

Right-click on the worksheet tab and "View Code"

Copy/paste this code into that module.

Private Sub Worksheet_Change(ByVal Target As Range)
Set r = Range("A:A") 'adjust to suit your range
If Intersect(Target, r) Is Nothing Then
Exit Sub
End If
vals = Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10) 'your 15 numbers will go here
nums = Array(8, 9, 6, 3, 7, 4, 20, 10, 16, 15) 'you need 15 colorindex numbers
For Each rr In r
icolor = 0
For i = LBound(vals) To UBound(vals)
If rr.Value = vals(i) Then
icolor = nums(i)
End If
Next
If icolor 0 Then
rr.EntireRow.Interior.ColorIndex = icolor
End If
Next
End Sub

This code has been tested with 10 numbers in the vals array
The nums array is the colorindex numbers from the Excel color palette

You will have to adjust and edit to suit.

For a list of the colorindex numbers see David McRitchie's site

http://www.mvps.org/dmcritchie/excel/colors.htm

When editing is complete, Alt + q to return to the Excel window.

As always, do all of this on a copy of the worksheet or workbook.


Gord Dibben MS Excel MVP

On Wed, 14 May 2008 09:30:02 -0700, Judy Rose
wrote:

I also need this help and am not sure how to access the VBE in the system, in
addition I am referencing a list of 15 numbers each corresponds to a
different background and need the background to fill an entire row selection
based on the number in the given column. This is for color tracking of
application data as the application works its way through the process each
number corresponds to a step in the process and I want the row of data to
change automatically when I change the number on the row.

Thank you.
--
Judy Rose Cohen

I would go with darker fill colors then color the font white.

Make changes as such...............

Next
If icolor < 0 Then
With rr
.Interior.ColorIndex = icolor
.Font.ColorIndex = 2
.Font.Bold = True
End With
End If
Next

BTW.........Your original post stated 15 numbers.

Excel has 56 colors in the color palette.


Gord

On Fri, 16 May 2008 11:13:00 -0700, Judy Rose
wrote:

Thank you. Only issue I had was coming up with enough colors different
enough to cover all my categories. Is there a way I could specify if a
certain color fill is used to also use a set pattern or font color? I
understood where your programming came from but wasn't sure where I could
indicate if icolor= (certain value) Then font for entire row = Bold/Italics,
etc.....How to get the program to acknowledge the fact that for a given color
for a row I need the change in font so it will be more visible. If this is
possible would you let me know.

Again, thank you for the help, it worked really well.

Originally I did only have 15, then realized I forgot one type of application
condition, where do I insert the code for the font, is it a modification of
the following; (sorry have a mental block when it comes to programming, you'd
think after 20+ years i wouldn't, but I do....)
Next
If icolor 0 Then
rr.EntireRow.Interior.ColorIndex = icolor
End If
Next
End Sub

Again thank you for your assistance

--
Judy Rose Cohen


"Gord Dibben" wrote:

I would go with darker fill colors then color the font white.

Make changes as such...............

Next
If icolor < 0 Then
With rr
.Interior.ColorIndex = icolor
.Font.ColorIndex = 2
.Font.Bold = True
End With
End If
Next

BTW.........Your original post stated 15 numbers.

Excel has 56 colors in the color palette.


Gord

On Fri, 16 May 2008 11:13:00 -0700, Judy Rose
wrote:

Thank you. Only issue I had was coming up with enough colors different
enough to cover all my categories. Is there a way I could specify if a
certain color fill is used to also use a set pattern or font color? I
understood where your programming came from but wasn't sure where I could
indicate if icolor= (certain value) Then font for entire row = Bold/Italics,
etc.....How to get the program to acknowledge the fact that for a given color
for a row I need the change in font so it will be more visible. If this is
possible would you let me know.

Again, thank you for the help, it worked really well.

Just reread the code and figured out what you were saying, so I gather than
that I cannot do another sub of the original sub to specify not only to
change the color to a specific for each icolor, but with certain icolor to
change the font on only those particular ones that are too dark to having the
font go white. I just had one that I used in the original that the color was
too dark and it was only that particular color I wanted the change in the
font. In my previous worksheets I had done the change of the rows to colors
manually each time and so I had certain patterns used with certain colors to
ensure they had visible fonts, however didn't see a way to do a case by case
so that for each value the fill would be the base color, plus a given
pattern, plus a given font, I saw something with specification for cases, but
with the array the formula worked better for my needs.
--
Judy Rose Cohen


"Gord Dibben" wrote:

I would go with darker fill colors then color the font white.

Make changes as such...............

Next
If icolor < 0 Then
With rr
.Interior.ColorIndex = icolor
.Font.ColorIndex = 2
.Font.Bold = True
End With
End If
Next

BTW.........Your original post stated 15 numbers.

Excel has 56 colors in the color palette.


Gord

On Fri, 16 May 2008 11:13:00 -0700, Judy Rose
wrote:

Thank you. Only issue I had was coming up with enough colors different
enough to cover all my categories. Is there a way I could specify if a
certain color fill is used to also use a set pattern or font color? I
understood where your programming came from but wasn't sure where I could
indicate if icolor= (certain value) Then font for entire row = Bold/Italics,
etc.....How to get the program to acknowledge the fact that for a given color
for a row I need the change in font so it will be more visible. If this is
possible would you let me know.

Again, thank you for the help, it worked really well.

If you want to change the font color to white as suggested, just alter the code
as I posted in my last reply.

If you want to have each case a different colored font.

Private Sub Worksheet_Change(ByVal Target As Range)
Set r = Range("A1:A50")
If Intersect(Target, r) Is Nothing Then
Exit Sub
End If
vals = Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10)
nums = Array(8, 9, 6, 3, 7, 4, 20, 10, 16, 15)
fnts = Array(2, 3, 4, 5, 6, 7, 8, 9, 10, 11)
For Each rr In r
icolor = 0
jcolor = 0
For i = LBound(vals) To UBound(vals)
If rr.Value = vals(i) Then
icolor = nums(i)
jcolor = fnts(i)
End If
Next
If icolor 0 And jcolor 0 Then
rr.Interior.ColorIndex = icolor
rr.Font.ColorIndex = jcolor
End If
Next
End Sub


Gord

On Tue, 20 May 2008 07:26:00 -0700, Judy Rose
wrote:

Originally I did only have 15, then realized I forgot one type of application
condition, where do I insert the code for the font, is it a modification of
the following; (sorry have a mental block when it comes to programming, you'd
think after 20+ years i wouldn't, but I do....)
Next
If icolor 0 Then
rr.EntireRow.Interior.ColorIndex = icolor
End If
Next
End Sub

Again thank you for your assistance

Thank you again, guess you didn't see my follow up post where I was asking
precisely how to do what you just described. Thank you again for all of your
help. This should work very nicely.
--
Judy Rose Cohen


"Gord Dibben" wrote:

If you want to change the font color to white as suggested, just alter the code
as I posted in my last reply.

If you want to have each case a different colored font.

Private Sub Worksheet_Change(ByVal Target As Range)
Set r = Range("A1:A50")
If Intersect(Target, r) Is Nothing Then
Exit Sub
End If
vals = Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10)
nums = Array(8, 9, 6, 3, 7, 4, 20, 10, 16, 15)
fnts = Array(2, 3, 4, 5, 6, 7, 8, 9, 10, 11)
For Each rr In r
icolor = 0
jcolor = 0
For i = LBound(vals) To UBound(vals)
If rr.Value = vals(i) Then
icolor = nums(i)
jcolor = fnts(i)
End If
Next
If icolor 0 And jcolor 0 Then
rr.Interior.ColorIndex = icolor
rr.Font.ColorIndex = jcolor
End If
Next
End Sub


Gord

On Tue, 20 May 2008 07:26:00 -0700, Judy Rose
wrote:

Originally I did only have 15, then realized I forgot one type of application
condition, where do I insert the code for the font, is it a modification of
the following; (sorry have a mental block when it comes to programming, you'd
think after 20+ years i wouldn't, but I do....)
Next
If icolor 0 Then
rr.EntireRow.Interior.ColorIndex = icolor
End If
Next
End Sub

Again thank you for your assistance

Thanks for the feedback.

You can alter the fnts = Array(2, 3, 4, 5, 6, 7, 8, 9, 10, 11)

fnts = Array(2, 3, 2, 2, 2, 2, 5, etc) to match just the darker or lighter nums
array

A lot of fiddling but can be done.


Gord

On Tue, 20 May 2008 10:03:00 -0700, Judy Rose
wrote:

Thank you again, guess you didn't see my follow up post where I was asking
precisely how to do what you just described. Thank you again for all of your
help. This should work very nicely.

Ok, I tinkered, got the thing to work on the column where the number is
specified that the nums and fnts refer to, also used the with rr that you set
up to indicate for all fonts to use bold and that worked very well, what I
don't understand with the code is what did I do that now for the font it only
shows in the first column, I need the font to also carry across the
row.....didn't see where to put in the reference for jcolor to carry same as
icolor. Not sure where I went wrong....

again thank you for your assistance.
--
Judy Rose Cohen


"Gord Dibben" wrote:

Thanks for the feedback.

You can alter the fnts = Array(2, 3, 4, 5, 6, 7, 8, 9, 10, 11)

fnts = Array(2, 3, 2, 2, 2, 2, 5, etc) to match just the darker or lighter nums
array

A lot of fiddling but can be done.


Gord

On Tue, 20 May 2008 10:03:00 -0700, Judy Rose
wrote:

Thank you again, guess you didn't see my follow up post where I was asking
precisely how to do what you just described. Thank you again for all of your
help. This should work very nicely.

Next
If icolor 0 And jcolor 0 Then
With rr.EntireRow
.Interior.ColorIndex = icolor
.Font.ColorIndex = jcolor
End With
End If
Next
End Sub



Gord

On Tue, 20 May 2008 10:37:00 -0700, Judy Rose
wrote:

Ok, I tinkered, got the thing to work on the column where the number is
specified that the nums and fnts refer to, also used the with rr that you set
up to indicate for all fonts to use bold and that worked very well, what I
don't understand with the code is what did I do that now for the font it only
shows in the first column, I need the font to also carry across the
row.....didn't see where to put in the reference for jcolor to carry same as
icolor. Not sure where I went wrong....

again thank you for your assistance.

Thank you....I should have seen that, sorry I didn't
--
Judy Rose Cohen


"Gord Dibben" wrote:

Next
If icolor 0 And jcolor 0 Then
With rr.EntireRow
.Interior.ColorIndex = icolor
.Font.ColorIndex = jcolor
End With
End If
Next
End Sub



Gord

On Tue, 20 May 2008 10:37:00 -0700, Judy Rose
wrote:

Ok, I tinkered, got the thing to work on the column where the number is
specified that the nums and fnts refer to, also used the with rr that you set
up to indicate for all fonts to use bold and that worked very well, what I
don't understand with the code is what did I do that now for the font it only
shows in the first column, I need the font to also carry across the
row.....didn't see where to put in the reference for jcolor to carry same as
icolor. Not sure where I went wrong....

again thank you for your assistance.