Conditional Formatting - > 3 Conditions

  • Thread starter Thread starter Bdavis
  • Start date Start date
B

Bdavis

I'd like to conditionally format the cells in column C
based on the cells in Column A. I can't use the the
wizard becasue I need about six conditions and the wizard
allows only three.

Example, if A2 = 2, then C2 = GREEN ; IF A3 = 4, then C3 =
Red; etc.
 
Hi

you need to use code for this, here's an example that uses 9 conditions -
this code needs to go in the code area of the sheet where you want to use
the conditional formatting on (ie if this is to go on sheet 1, right mouse
click on the sheet tab of sheet 1, choose view code & copy & paste the code
there)

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Range("B6")) Is Nothing Then
With Target
Select Case .Value
Case 1: Range("B6").Font.ColorIndex = 4
Case 2: Range("B6").Font.ColorIndex = 3
Case 3: Range("B6").Font.ColorIndex = 0
Case 4: Range("B6").Font.ColorIndex = 6
Case 5: Range("B6").Font.ColorIndex = 13
Case 6: Range("B6").Font.ColorIndex = 46
Case 7: Range("B6").Font.ColorIndex = 11
Case 8: Range("B6").Font.ColorIndex = 7
Case 9: Range("B6").Font.ColorIndex = 55
End Select
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

--- this turns the font of B6 a different colour depending on what value
(between 1 & 9) is entered in the cell.

Hope this helps
Cheers
JulieD
 
Hi bdavis,
I think the example at
http://www.mvps.org/dmcritchie/excel/event.htm#case
would be a bit easier to adapt, but it will still require ability to
change to your own usage. You will also see a table of
colorindex colors that is more commonly seen in on my colors.htm
page. In a pinch (as a visitor on someone else's computer), you
can see a table in the VBE HELP for colorindex.

The question used row and column designations and was clear
but the example you supplied leads to a lot of ambiguity.
Stick to row or column if that is what you mean rather
than specific cell addresses. Why is only cell A2 checked
for a color then cell A3 checked for a different color -- do you
see what I mean by ambiguity.

If you are going to test one cell and color another you might
want to use something like
cell.offset(0,1),interior.colorindex = 27
though I think maybe coloring the entire row in the example
I pointed to might be more appropriate. If you are using
more than five colors you might want to rethink what you are
doing because it might look very busy and confusing. If for
others, not every one is going to see colors as you see them
on your monitor. Laptops are notorious for making colors look
the same if viewed directly and some people are color impaired.

You can modify the example on my webpage to your use with

Set cRange = Intersect(Range("A:A"), Range(Target(1).Address))
intead of
Set cRange = Intersect(Range("B4:J19"), Range(Target(1).Address))

cell.Offset(0, 1).Interior.colorindex = vColor
instead of:
cell.Interior.ColorIndex = vColor

install the event macro by right click on the worksheet tab, view code,
and placing your code there. You must have macros enabled.
Will apply to only this one worksheet. Change Event macro is only
for changes you make, not for values changed by use of formulas.
XL97 has at least another restriction.

Macros will not run if the security setting is set to High, Check under Tools, Macro, security, set to Medium. If the security
setting is set a High and you try to run macros in the workbook you will see the following message:
The macros in the project are disabled. Please refer to the online help or documentation of the host application to determine how
to enable macros.
 
Back
Top