Help with conditional formating

  • Thread starter Thread starter PO
  • Start date Start date
P

PO

Hi,

I'm running Excel 2003, Sp2.
I'm using conditional formatting to change the background color on some
worksheet cells:

Cellvalue = Color
============
A = red
B = green
C = blue

This works fine but due to changes in the workbook I now also need to be
able to format a fourth cellvalue, D = yellow. The built-in function
however only lets me set up conditional formatting for three values.

Is there any way to achieve this?

TIA
Pete
 
Pete,

Use simple formatting to color the cell yellow. CF will take over for the first three values, and
it will be yellow for D. If you have more than that, or need to have a different color for blank
(which would be a fifth condition), then you will need to use the worksheet_change event with code
to control the color fill.

HTH,
Bernie
MS Excel MVP
 
Pete,

The example code below will apply colors to column A. Copy the code, right-click the sheet tab,
select "View Code" and paste the code into the window that appears.

HTH,
Bernie
MS Excel MVP


Private Sub Worksheet_Change(ByVal Target As Range)
Dim myCell As Range
Dim myR As Range

Set myR = Intersect(Target, Range("A:A"))

If myR Is Nothing Then Exit Sub

Application.EnableEvents = False
For Each myCell In myR
If myCell.Value = "A" Then myCell.Interior.ColorIndex = 3
If myCell.Value = "B" Then myCell.Interior.ColorIndex = 41
If myCell.Value = "C" Then myCell.Interior.ColorIndex = 50
If myCell.Value = "D" Then myCell.Interior.ColorIndex = 6
If myCell.Value = "E" Then myCell.Interior.ColorIndex = 46
If myCell.Value = "" Then myCell.Interior.ColorIndex = xlNone
Next myCell
Application.EnableEvents = True
End Sub
 
Thanks Bernie, works perfect!

/Pete


Bernie Deitrick said:
Pete,

The example code below will apply colors to column A. Copy the code,
right-click the sheet tab, select "View Code" and paste the code into the
window that appears.

HTH,
Bernie
MS Excel MVP


Private Sub Worksheet_Change(ByVal Target As Range)
Dim myCell As Range
Dim myR As Range

Set myR = Intersect(Target, Range("A:A"))

If myR Is Nothing Then Exit Sub

Application.EnableEvents = False
For Each myCell In myR
If myCell.Value = "A" Then myCell.Interior.ColorIndex = 3
If myCell.Value = "B" Then myCell.Interior.ColorIndex = 41
If myCell.Value = "C" Then myCell.Interior.ColorIndex = 50
If myCell.Value = "D" Then myCell.Interior.ColorIndex = 6
If myCell.Value = "E" Then myCell.Interior.ColorIndex = 46
If myCell.Value = "" Then myCell.Interior.ColorIndex = xlNone
Next myCell
Application.EnableEvents = True
End Sub
Click to expand...
 
Back
Top