autoformatting with more than 3 conditions

[Haroon]

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

 

[Bob Phillips]

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.

 

[Haroon]

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

 

[Sheeloo]

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.

 

[Gord Dibben]

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 D1100 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("D1100") '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

 

[Haroon]

thanks guys

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 D1100 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("D1100") '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

 

[Henk Bosschaart]

Hi Bob,

I tried your solution and it seems fine, but the color only changes whith
manual input or "cell by cell" confirmation. If I copy&paste a range, or if I
use a formula, it doesn't work.
Any solution on cells which already have data?

 

[Roger Govier]

Hi Henk

have you amended the code to suit your range of data entry

Const WS_RANGE As String = "H1:H10" '<=== change to suit

The code only operates on cells within the range H1:H10
Change the values to suit the area of your sheet where you want these
changes to occur, and all should be well.

 

[Henk Bosschaart]

Hi Roger,

Yes, I did; however my "range" was the entire column D...
But,
I recently installed excel2007 and that solved this problem
Thanks anyway!