autoformatting with more than 3 conditions

  • Thread starter Thread starter Haroon
  • Start date Start date
H

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 :)
 
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.
 
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.
 
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
 
thanks guys

Gord Dibben said:
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
Click to expand...
 
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?
 
Hi Henk

have you amended the code to suit your range of data entry
Const WS_RANGE As String = "H1:H10" '<=== change to suit
Click to expand...

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.
 
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!
 
Back
Top