Multiple conditional formatting Excel 2003

Question

Hi,

I have a conditional formatting problem with MS Excel 2003. I need 5 conditions, but have only three.

For example I need cell A1 (it’s font color and background) to change based on the value of cell B1:

-         if value of B1 is -1 than A1 should change to: background “red” and font “white”

-         if value of B1 is 3 than A1 should change to: background “blue” and font “white”

-         if value of B1 is 4 than A1 should change to: background “green” and font “white”

-         if value of B1 is 5 than A1 should change to: background “orange” and font “black”

-         if value of B1 is 7 than A1 should change to: background “black” and font “white”

I’m have very basic knowledge in VBA…

Thanks in advance!

Svetlin

Answer 1

You can imitate more than 3 conditional formatting rules in Excel 2003 by mimicking the CF behavior with a Worksheet_Change macro event. Right-click the name tab of the worksheet and choose View Code. When the VBE opens, paste the following into the pane titles something like Book1 - Sheet1 (Code),

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Me.Columns(2)) Is Nothing Then

On Error GoTo FallThrough

Application.EnableEvents = False

Dim b As Range

For Each b In Intersect(Target, Me.Columns(2))

Select Case b.Value

Case (-1)

b.Offset(0, -1).Interior.ColorIndex = 9

b.Offset(0, -1).Font.ColorIndex = 2

Case 3

b.Offset(0, -1).Interior.ColorIndex = 5

b.Offset(0, -1).Font.ColorIndex = 2

Case 4

b.Offset(0, -1).Interior.ColorIndex = 10

b.Offset(0, -1).Font.ColorIndex = 2

Case 5

b.Offset(0, -1).Interior.ColorIndex = 45

b.Offset(0, -1).Font.ColorIndex = 1

Case 7

b.Offset(0, -1).Interior.ColorIndex = 1

b.Offset(0, -1).Font.ColorIndex = 2

Case Else

'do nothing

b.Offset(0, -1).Interior.Pattern = xlNone

b.Offset(0, -1).Font.ColorIndex = xlAutomatic

End Select

Next b

End If

FallThrough:

Application.EnableEvents = True

End Sub

Edit the integers used for the .ColorIndex colors if you need to (see table below). Tap Alt+Q to return to your worksheet.

Here is an image of an Excel table demonstrating the basic ColorIndex colors:

Answer 2

Thank you guys! I appreciate your effort very much!

Answer 3

Hi,

try this....

step1

save as your workbook with extension .xlsm (macros enabled)

step2

assuming that data is on sheet1

Right click on sheet1 tab, select View Code and paste in the following:

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("B1")) Is Nothing Then

Select Case Target.Value

Case Is = "-1"

'interior=red, font=white

Target.Offset(, -1).Interior.Color = RGB(255, 0, 0)

Target.Offset(, -1).Font.Color = RGB(255, 255, 255)

Case Is = "3"

'interior=blue, font=white

Target.Offset(, -1).Interior.Color = RGB(0, 0, 255)

Target.Offset(, -1).Font.Color = RGB(255, 255, 255)

Case Is = "4"

'interior=green, font=white

Target.Offset(, -1).Interior.Color = RGB(0, 255, 0)   '<<< or 128

Target.Offset(, -1).Font.Color = RGB(255, 255, 255)

Case Is = "5"

'interior=orange, font=black

Target.Offset(, -1).Interior.Color = RGB(255, 102, 0)

Target.Offset(, -1).Font.Color = RGB(0, 0, 0)

Case Is = "7"

'interior=black, font=white

Target.Offset(, -1).Interior.Color = RGB(0, 0, 0)

Target.Offset(, -1).Font.Color = RGB(255, 255, 255)

Case Else

'interior=no color, font=black

Target.Offset(, -1).Interior.ColorIndex = xlNone

Target.Offset(, -1).Font.Color = RGB(0, 0, 0)

End Select

End If

End Sub

step3

press Alt+Q to close Visual Basic

update,

each time you change a value in cell B1

colors

http://www.endprod.com/colors/

Answer 4

I think this link will help with your problem:

More than 3 Conditional Formats in Excel 2003

http://chandoo.org/wp/2008/10/14/more-than-3-conditional-formats-in-excel/

One of the most popular posts on this blog is **how to become excel conditional formatting rock star**. Quite a few commenters there asked me if there is a way to get more than 3 (or 4) conditional formats in excel 2003. Like what you see below:

Here are some more options to look at

More than Three Conditional Formats

http://excel.tips.net/Pages/T002439\_More\_than\_Three\_Conditional\_Formats.html

Summary: Conditional formatting is a powerful feature of Excel, but it has traditionally been limited to three explicit formats. This tip examines ways around this limitation, along with a look at how the limitation was entirely removed in Excel 2007. (This tip works with Excel 97 | 2000 | 2002 | 2003 | 2007.)

Answer 5

Hi,check the link as follow to overcome this problem

http://www.ozgrid.com/VBA/excel-conditional-formatting-limit.htm

to get the color pallete look here

http://dmcritchie.mvps.org/excel/colors.htm