autoformatting with more than 3 conditions
 

hi

i have a cell with more than 3 options, and would like to change the state
of another cell if the select a value in the dropdown list with other
cells/row changing colours.

i can only do 3 conditional formatting which is not good for me, how can i
have more than 3?

anyone got ideas?

thanks in advance :)

autoformatting with more than 3 conditions
 

As an example


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

"Haroon" wrote in message
...
hi

i have a cell with more than 3 options, and would like to change the state
of another cell if the select a value in the dropdown list with other
cells/row changing colours.

i can only do 3 conditional formatting which is not good for me, how can i
have more than 3?

anyone got ideas?

thanks in advance :)

autoformatting with more than 3 conditions
 

sorry it title should be 'conditional formatting with more than 3 conditions'

"Haroon" wrote:

hi

i have a cell with more than 3 options, and would like to change the state
of another cell if the select a value in the dropdown list with other
cells/row changing colours.

i can only do 3 conditional formatting which is not good for me, how can i
have more than 3?

anyone got ideas?

thanks in advance :)

autoformatting with more than 3 conditions
 

Unless you use Excel 2007 you can not have more than three conditions using
conditional formatting.

You can have upto three more using Cell FORMATS but with limited options.

"Haroon" wrote:

sorry it title should be 'conditional formatting with more than 3 conditions'

"Haroon" wrote:

hi

i have a cell with more than 3 options, and would like to change the state
of another cell if the select a value in the dropdown list with other
cells/row changing colours.

i can only do 3 conditional formatting which is not good for me, how can i
have more than 3?

anyone got ideas?

thanks in advance :)

autoformatting with more than 3 conditions
 

If you want to post some specific cell references we could tailor something
using event code.

Here is an example of such code that colors cells in D1:D100 as you enter
data in them.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim R As Range
Dim iColor As Long
Set R = Range("D1:D100") 'adjust to suit
If Intersect(Target, R) Is Nothing Or _
Target.Count 1 Then Exit Sub
Vals = Array("WEST", "EAST", "NORTH", "SOUTH")
Nums = Array(3, 5, 10, 6)
For i = LBound(Vals) To UBound(Vals)
With Target
If UCase(.Value) = Vals(i) Then iColor = Nums(i)
If UCase(.Value) = Vals(i) Then .Font.Bold = True
End With
Next
With Target
.Interior.ColorIndex = iColor
End With
End Sub


Gord Dibben MS Excel MVP

On Wed, 7 Jan 2009 08:26:01 -0800, Haroon
wrote:

hi

i have a cell with more than 3 options, and would like to change the state
of another cell if the select a value in the dropdown list with other
cells/row changing colours.

i can only do 3 conditional formatting which is not good for me, how can i
have more than 3?

anyone got ideas?

thanks in advance :)

autoformatting with more than 3 conditions
 

thanks guys

"Gord Dibben" wrote:

If you want to post some specific cell references we could tailor something
using event code.

Here is an example of such code that colors cells in D1:D100 as you enter
data in them.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim R As Range
Dim iColor As Long
Set R = Range("D1:D100") 'adjust to suit
If Intersect(Target, R) Is Nothing Or _
Target.Count 1 Then Exit Sub
Vals = Array("WEST", "EAST", "NORTH", "SOUTH")
Nums = Array(3, 5, 10, 6)
For i = LBound(Vals) To UBound(Vals)
With Target
If UCase(.Value) = Vals(i) Then iColor = Nums(i)
If UCase(.Value) = Vals(i) Then .Font.Bold = True
End With
Next
With Target
.Interior.ColorIndex = iColor
End With
End Sub


Gord Dibben MS Excel MVP

On Wed, 7 Jan 2009 08:26:01 -0800, Haroon
wrote:

hi

i have a cell with more than 3 options, and would like to change the state
of another cell if the select a value in the dropdown list with other
cells/row changing colours.

i can only do 3 conditional formatting which is not good for me, how can i
have more than 3?

anyone got ideas?

thanks in advance :)