Hi All:

I would like to use conditional formatting on a spreadsheet where a row in a
spread sheet's color depends on the value of one of the cells on a row,
called the status cell. However, I would like to have six colors
representing six different values in the status cell. Is there a way to
implement this conditional formatting?

Thanks in advance,

steve-o

You can use a VBA to accomplish this. Microsoft only allows 3 Conditional
Formats. Here is a code to accomplish the same task


Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "H1:H10"

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
Select Case .Value
Case 1: .Interior.ColorIndex = 3 'red
Case 2: .Interior.ColorIndex = 6 'yellow
Case 3: .Interior.ColorIndex = 5 'blue
Case 4: .Interior.ColorIndex = 10 'green
End Select
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

This code needs to be placed in the worksheet that it will be used in, not a
module. So in retrospect, Alt+11, select "Sheet 1" and then copy and paste
this code.


--
If this reply was helpful, please indicate that your question has been
answered to help others find anwsers to similar questions.

www.silverbirddesigns.com

Fighting Texas Aggie Class of 2009


"steve-o" wrote:

Hi All:

I would like to use conditional formatting on a spreadsheet where a row in a
spread sheet's color depends on the value of one of the cells on a row,
called the status cell. However, I would like to have six colors
representing six different values in the status cell. Is there a way to
implement this conditional formatting?

Thanks in advance,

steve-o

If you have Excel 2007, yes. If not your would need VBA


http://www.xldynamic.com/source/xld.....Download.html


unless it would fit these requirements

http://www.mcgimpsey.com/excel/conditional6.html


--


Regards,


Peo Sjoblom


"steve-o" wrote in message
...
Hi All:

I would like to use conditional formatting on a spreadsheet where a row in
a
spread sheet's color depends on the value of one of the cells on a row,
called the status cell. However, I would like to have six colors
representing six different values in the status cell. Is there a way to
implement this conditional formatting?

Thanks in advance,

steve-o

Thanks for your answer.

I tried downloading the file from the first link and winzip gave me the
following message:

Cannot Open File: it does not appear to be a valid archive. If you
downloaded this file, try downloading it again."

is there another link where I could download it?

Thanks ,

"Peo Sjoblom" wrote:

If you have Excel 2007, yes. If not your would need VBA


http://www.xldynamic.com/source/xld.....Download.html


unless it would fit these requirements

http://www.mcgimpsey.com/excel/conditional6.html


--


Regards,


Peo Sjoblom


"steve-o" wrote in message
...
Hi All:

I would like to use conditional formatting on a spreadsheet where a row in
a
spread sheet's color depends on the value of one of the cells on a row,
called the status cell. However, I would like to have six colors
representing six different values in the status cell. Is there a way to
implement this conditional formatting?

Thanks in advance,

steve-o

Hi Thanks for your reply:

I had one question- in my spreadsheet, I have about 10 cells in a row. when
I change the value of one cell in the row, the color of the rest of the cells
of the entire row changes based on the value of one cell. Is there a
modification in this code that will allow me to do this?

Thanks in advance,

"Texas Aggie" wrote:

You can use a VBA to accomplish this. Microsoft only allows 3 Conditional
Formats. Here is a code to accomplish the same task


Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "H1:H10"

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
Select Case .Value
Case 1: .Interior.ColorIndex = 3 'red
Case 2: .Interior.ColorIndex = 6 'yellow
Case 3: .Interior.ColorIndex = 5 'blue
Case 4: .Interior.ColorIndex = 10 'green
End Select
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

This code needs to be placed in the worksheet that it will be used in, not a
module. So in retrospect, Alt+11, select "Sheet 1" and then copy and paste
this code.


--
If this reply was helpful, please indicate that your question has been
answered to help others find anwsers to similar questions.

www.silverbirddesigns.com

Fighting Texas Aggie Class of 2009


"steve-o" wrote:

Hi All:

I would like to use conditional formatting on a spreadsheet where a row in a
spread sheet's color depends on the value of one of the cells on a row,
called the status cell. However, I would like to have six colors
representing six different values in the status cell. Is there a way to
implement this conditional formatting?

Thanks in advance,

steve-o

What can I say, I just went to Bob's site and downloaded it and it worked
fine. I had no problems extracting both files.


--


Regards,


Peo Sjoblom


"steve-o" wrote in message
...
Thanks for your answer.

I tried downloading the file from the first link and winzip gave me the
following message:

Cannot Open File: it does not appear to be a valid archive. If you
downloaded this file, try downloading it again."

is there another link where I could download it?

Thanks ,

"Peo Sjoblom" wrote:

If you have Excel 2007, yes. If not your would need VBA


http://www.xldynamic.com/source/xld.....Download.html


unless it would fit these requirements

http://www.mcgimpsey.com/excel/conditional6.html


--


Regards,


Peo Sjoblom


"steve-o" wrote in message
...
Hi All:

I would like to use conditional formatting on a spreadsheet where a row
in
a
spread sheet's color depends on the value of one of the cells on a row,
called the status cell. However, I would like to have six colors
representing six different values in the status cell. Is there a way to
implement this conditional formatting?

Thanks in advance,

steve-o

I might add that if you are doing this on a regular basis it is much better
using an add-in then an event macro


--


Regards,


Peo Sjoblom



"Peo Sjoblom" wrote in message
...
What can I say, I just went to Bob's site and downloaded it and it worked
fine. I had no problems extracting both files.


--


Regards,


Peo Sjoblom


"steve-o" wrote in message
...
Thanks for your answer.

I tried downloading the file from the first link and winzip gave me the
following message:

Cannot Open File: it does not appear to be a valid archive. If you
downloaded this file, try downloading it again."

is there another link where I could download it?

Thanks ,

"Peo Sjoblom" wrote:

If you have Excel 2007, yes. If not your would need VBA


http://www.xldynamic.com/source/xld.....Download.html


unless it would fit these requirements

http://www.mcgimpsey.com/excel/conditional6.html


--


Regards,


Peo Sjoblom


"steve-o" wrote in message
...
Hi All:

I would like to use conditional formatting on a spreadsheet where a
row in
a
spread sheet's color depends on the value of one of the cells on a
row,
called the status cell. However, I would like to have six colors
representing six different values in the status cell. Is there a way
to
implement this conditional formatting?

Thanks in advance,

steve-o