4 conditional format

[Prashanth KR]

Hi,

In a range B1:AE1, I have formulae’s which converts the date from B2:AE2 to
days i.e., “Sunâ€, “Monâ€, “Tue†and so on. I only change the first day of
every month in B2 where all dates gets auto-populated in the subsequent range
of cells.

Now I want to give more than 3 Conditional formatting which stands as below:
If B1=Sat, change color to RED
If C1=Sun, change color to BLUE
If A1=Holiday, change color to ORANGE
If A1=Working, Change color to GREEN

These formatting needs to be applied to the range D1:AE2. I could succeed in
doing the first 3 by using the CF method which is limited to only 3 at a
time. I am using Excel 2003.

I don’t want the background to change to any default color in the range
D1:AE2 other than WHITE.

Please suggest a macro for the same. I shall be very grateful to you.

Thanks in advance,
Prashanth KR.

 

[Rick Rothstein \(MVP - VB\)]

Instead of a macro that you have to run manually, use this event code
instead which will react to changes in the range B1:AE1 just as the
Conditional Formatting would do...

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("B1:AE1")) Is Nothing Then
With Target
Select Case .Value
Case "Sat"
.Cells.Interior.ColorIndex = 3
Case "Sun"
.Cells.Interior.ColorIndex = 41
Case "Holiday"
.Cells.Interior.ColorIndex = 45
Case "Working"
.Cells.Interior.ColorIndex = 4
Case Else
.Cells.Interior.ColorIndex = xlNone
End Select
End With
End If
End Sub

Copy/Paste the code into the code window for the worksheet you want this
functionality on.

Rick

 

[Prashanth KR]

Hi Rick,

Thanks for the quick reply. It does work but not serving my purpose. I
think, I need to be more clear.

Actually in B1:AE1, I have a formulae i.e,. =TEXT(B2,"ddd");
=TEXT(B3,"ddd"); and so on till =TEXT(AE2,"ddd") which converts the dates in
B2:AE2 i.e. 1-May-2008 to "Thu"; 2-May-2008 to "Fri" and so on.

*** Now, I want to change the color of the range D1:AE2 by giving the
following conditions:

If B1=Sat, change color to RED
If C1=Sun, change color to BLUE

Also

There is a range A1:AE1 containing "Holiday" and "Working" in drop-down list.

If A1=Holiday, change color to ORANGE
If A1=Working, Change color to GREEN

***The color should change not only in the range B1:AE1 but also in D1:AE2
appropriately, if he conditions satisfies.

In this case it only changes in B1:AE1 and NOT in D1:AE2, and only when it
is entered manually. It does not change the background when their is formulae
or a drop-down list.

I know, its a two set of conditioning having the ranges B1:AE1 and A1:AE1
based on which the formatting should be applied to the third set of range
D1:AE2.

Iam sure their is some way out for this. Please help.

Thanks in advance,
Prashanth KR.