Conditional Formating

[Guest]

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]

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

 

[Guest]

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]

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)

 

[Francois via OfficeKB.com]

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.CFPlus.Download.html

I use this

 

[Guest]

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

Thnx

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.CFPlus.Download.html

I use this