Prev: Averaging weekly data into Months
Next: how do i merge the datas in 2 columns
From: L. Howard Kittle on 19 May 2010 14:30
Try this by Bob Phillips from a google search

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
Case 5: .Interior.ColorIndex = 46 'orange
Case 6: .Interior.ColorIndex = 8
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
Regards,
Howard

"Pomodoro" <Pomodoro(a)discussions.microsoft.com> wrote in message
news:D51F9BA3-C356-4EED-B8E4-76308D8D4581(a)microsoft.com...
> Hi,
> I have 6 different investigation area, one single name each, and I would
> like to set 6 different conditional formatting. But I'm not able to set
> more
> than 3.
>
> Any idea how can I manage it?
>
> Many thanks


From: Gord Dibben on 19 May 2010 14:38
Sample worksheet event code for 10 conditions and colors.

Adjust range, vals and nums to suit.

Private Sub Worksheet_Change(ByVal Target As Range)
Set r = Range("A1:A100")
If Intersect(Target, r) Is Nothing Then
Exit Sub
End If
vals = Array("C", "D", "G", "H", "K", "L", "O", "S", "C", "X") 'conditions
nums = Array(8, 9, 6, 3, 7, 4, 20, 10, 13, 15) ' color fill
For Each rr In r
icolor = 0
For i = LBound(vals) To UBound(vals)
If UCase(rr.Value) = vals(i) Then
icolor = nums(i)
End If
Next
If icolor > 0 Then
rr.Interior.ColorIndex = icolor
End If
Next
End Sub

This is event code. Right-click on the sheet tab and "View Code".
Copy/paste into that sheet module. Make your edits then Alt + q to return
to Excel.


Gord Dibben MS Excel MVP


On Wed, 19 May 2010 02:23:01 -0700, Pomodoro
<Pomodoro(a)discussions.microsoft.com> wrote:

>Hi,
>I have 6 different investigation area, one single name each, and I would
>like to set 6 different conditional formatting. But I'm not able to set more
>than 3.
>
>Any idea how can I manage it?
>
>Many thanks

First  |  Prev  | 
Pages: 1 2
Prev: Averaging weekly data into Months
Next: how do i merge the datas in 2 columns