How do I set up more than 3 conditional formatting?

  • Thread starter Thread starter Pomodoro
  • Start date Start date
P

Pomodoro

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
 
Hi Gary!
Sorry to answer you late, I supposed to receive a notification mail, but I
didn't.
So, my conditions is like this:
if mycellvalue = "Services" then set cell pattern color to red else
if mycellvalue = "Technology" then set cell pattern color to gray else
if mycellvalue = "Green" then set cell pattern color to green else
if mycellvalue = "Service Innovations" then set cell pattern color to orange
else
and so on for at least 6 different Innovation Areas.
 
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
 
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
 
Back
Top