Conditional formating 7 conditions

**ABSTRAKTUS** · 04-16-2011, 03:36 PM

Hi all,

I am running XL 2003 and it gives me only 3 conditions on conditional formating. But I need 7.
I have a code but I can't adapt it to my new project as layout is totally different. Could someone have a look where changes need to be made in a code?

Cells to be affected are K12:AE12

Code:

Private Sub Worksheet_Change(ByVal Target As Range)
If ActiveCell.Column = 4 Then
ActiveCell.Font.ColorIndex = 1
If ActiveCell.Value = "Black" Then ActiveCell.Font.ColorIndex = 2
If ActiveCell.Value = "Orange" Then ActiveCell.Interior.ColorIndex = 44
If ActiveCell.Value = "Yellow" Then ActiveCell.Interior.ColorIndex = 6
If ActiveCell.Value = "Pink" Then ActiveCell.Interior.ColorIndex = 38
If ActiveCell.Value = "Blue" Then ActiveCell.Interior.ColorIndex = 41
If ActiveCell.Value = "Green" Then ActiveCell.Interior.ColorIndex = 4
If ActiveCell.Value = "Red" Then ActiveCell.Interior.ColorIndex = 3
If ActiveCell.Value = "Black" Then ActiveCell.Interior.ColorIndex = 1
End If
End Sub

Thanks a lot!

**contaminated** · 04-16-2011, 03:42 PM

Maybe this one

Please Login or Register  to view this content.

**TMS** · 04-16-2011, 03:52 PM

Please note you should put CODE tags around your code.

Please Login or Register  to view this content.

Regards

**ABSTRAKTUS** · 04-16-2011, 03:56 PM

Hi VUSAL,

It doesn't work for some reason... Is part of code If .Column = 4 Then ActiveCell.Font.ColorIndex = 1 correct?

**contaminated** · 04-16-2011, 03:57 PM

No... Please take a look at code once again I've just edited it.
And can you explain, what should this part of code to do? Check whether cells column number is 4 and then color its font color?

**ABSTRAKTUS** · 04-16-2011, 04:02 PM

Both codes don't work... See attached.

**contaminated** · 04-16-2011, 04:12 PM

What about this one. Seems it worked..

Please Login or Register  to view this content.

BTW great workbook))

PS Real Madrid vs Barcelona has just started... I'll enjoy the game))) Hope Barsa wins))

**ABSTRAKTUS** · 04-16-2011, 04:16 PM

Thanks. This worked well.

VUSAL, you have helped me quite a few times. Can you please have a look at the other thread as it is not solved and nobody is able to help me.

Link:

http://www.excelforum.com/excel-gene...n-another.html

Thanks

**TMS** · 04-16-2011, 04:36 PM

The reason that the Worksheet Change event is not working is that the cells you asked to monitor contain formulae. Changes in formulae do not trigger the Change event.

Contaminated's solution uses the Selection Change event. The only down side is that it will reformat all those cells every time you move between cells. It may be no big deal but it is an overhead you need to be aware of.

Regards