I have a conditional formating for a row on cells. The Formating changes the
color of a row if it includes a certain value. However the formula needs to
check for 10 diffrent possible values, and conditional formating only offers
three!
Is there a way around this?

Thanks
Zee

'-----------------------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
'-----------------------------------------------------------------
Const WS_RANGE As String = "H1:H10" '<=== change to suit

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
'etc.
End Select
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Zee" wrote in message
...
I have a conditional formating for a row on cells. The Formating changes
the
color of a row if it includes a certain value. However the formula needs
to
check for 10 diffrent possible values, and conditional formating only
offers
three!
Is there a way around this?

Thanks
Zee

Thanks alot bob,

But im having some problems understanding what this code does,

with condidtional formating i used such a formula:

=$D7="X" <<<< so when D7 is X it will make the row Yellow or other color
that was set.

I was wondering if i have to insert such a code in? and which parts of the
code should be adjusted? im kinda of new to this.

Thanks




"Bob Phillips" wrote:


'-----------------------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
'-----------------------------------------------------------------
Const WS_RANGE As String = "H1:H10" '<=== change to suit

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
'etc.
End Select
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Zee" wrote in message
...
I have a conditional formating for a row on cells. The Formating changes
the
color of a row if it includes a certain value. However the formula needs
to
check for 10 diffrent possible values, and conditional formating only
offers
three!
Is there a way around this?

Thanks
Zee

I had hoped it was self-explanatory.

You can't use CF because it only allows three conditions as you found, so
you need VBA. I gave you an example VBA code that provides 4 conditions, you
need to extend it and modify it for your colours and your conditions.

It is event code, so as soon as any value in the target range, which I
pre-loaded as H1:H10 but you can change, changes, it fires the code, which
checks that value and sets the colour accordingly.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Zee" wrote in message
...
Thanks alot bob,

But im having some problems understanding what this code does,

with condidtional formating i used such a formula:

=$D7="X" <<<< so when D7 is X it will make the row Yellow or other color
that was set.

I was wondering if i have to insert such a code in? and which parts of the
code should be adjusted? im kinda of new to this.

Thanks




"Bob Phillips" wrote:


'-----------------------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
'-----------------------------------------------------------------
Const WS_RANGE As String = "H1:H10" '<=== change to suit

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
'etc.
End Select
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Zee" wrote in message
...
I have a conditional formating for a row on cells. The Formating
changes
the
color of a row if it includes a certain value. However the formula
needs
to
check for 10 diffrent possible values, and conditional formating only
offers
three!
Is there a way around this?

Thanks
Zee

Bob Phillips wrote:
I had hoped it was self-explanatory.

You can't use CF because it only allows three conditions as you found, so
you need VBA. I gave you an example VBA code that provides 4 conditions, you
need to extend it and modify it for your colours and your conditions.

It is event code, so as soon as any value in the target range, which I
pre-loaded as H1:H10 but you can change, changes, it fires the code, which
checks that value and sets the colour accordingly.

Thanks alot bob,

[quoted text clipped - 49 lines]



Try this site

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

I use this


Thanks
Zee

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200608/1

Bob and Francois, thanks alot for your help, i will check the site and try
again.

Thnx

"Francois via OfficeKB.com" wrote:

Bob Phillips wrote:
I had hoped it was self-explanatory.

You can't use CF because it only allows three conditions as you found, so
you need VBA. I gave you an example VBA code that provides 4 conditions, you
need to extend it and modify it for your colours and your conditions.

It is event code, so as soon as any value in the target range, which I
pre-loaded as H1:H10 but you can change, changes, it fires the code, which
checks that value and sets the colour accordingly.

Thanks alot bob,

[quoted text clipped - 49 lines]



Try this site

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

I use this


Thanks
Zee

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200608/1