4 conditional format

  • Thread starter Thread starter Prashanth KR
  • Start date Start date
P

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.
 
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
 
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.
 
Back
Top