More than 3 conditions to a cell

  • Thread starter Thread starter Excel Doofus
  • Start date Start date
E

Excel Doofus

Greetings!

I am trying to find a way to have more than 3 conditions
when applying conditional formatting to a cell.

Example: We want managers to choose from 6 items in a
drop-down list for cells (which works great using a data
validation rule, but which also sort of messes up a
nested IF) in a report. Using a stylized font, we'd like
to have each choice from the in-cell drop-down be a
different color. I can get four colors (three conditions
adn one non-condition), but would love to add the last
two colors; Excel stops me at 3 conditional formats.

Any ideas? What am I missing? Is this possible?

I have zero VBA skills, btw. :-(

Many thanks,

Excel Doofus
 
This is the sort of code you need as worksheet event code, which is
worksheet event code

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("A20")) Is Nothing Then
Select Case Target.Value
Case 1: Target.Interior.ColorIndex = 1
Case 2: Target.Interior.ColorIndex = 2
Case 3: Target.Interior.ColorIndex = 3
Case 4: Target.Interior.ColorIndex = 4
Case 5: Target.Interior.ColorIndex = 5
Case 6: Target.Interior.ColorIndex = 6
End Select
End If

End Sub

but if you zero VBA, you need to tell us what the 6 values are, what colour
you want to set it, where the cell will reside etc.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Bob,

Here is the data you asked for. I am grateful for your
help!


The 6 values and what colour: "C" (no quotes, just a
capital C)- blue; "G" - green; "R" - red; "Y" -
yellow; "N" - black; "-" (hyphen) - orange.

where the cell will reside: a range of cells 20 rows high
by 15 columns wide, specifically D2:R21.

Thanks!

Excel Doofus
 
You could modify Bob's code:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Cells.Count > 1 Then Exit Sub
If Not Intersect(Target, Me.Range("d2:r21")) Is Nothing Then
Select Case LCase(Target.Value)
Case "c": Target.Interior.ColorIndex = 1
Case "g": Target.Interior.ColorIndex = 2
Case "r": Target.Interior.ColorIndex = 3
Case "y": Target.Interior.ColorIndex = 4
Case "n": Target.Interior.ColorIndex = 5
Case Else: Target.Interior.ColorIndex = xlNone
End Select
End If

End Sub

but since there's lots of shades of green, you should record a macro when you
shade some cells. Then steal those numbers and plop them into the code.
 
Back
Top