Help with conditional formating

[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

 

[Bernie Deitrick]

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

 

[PO]

Bernie,

Could you give an example of how the formating code would look like?

Regards
Pete

 

[Bernie Deitrick]

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

 

[PO]

Thanks Bernie, works perfect!

/Pete

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