Conditional Formatting - tricky one ( Atleast for me )

[javedmati]

Hi,

I am trying to do the following.

Thu Fri Sat Sun Mon Tue Wed
-----------------------------------------------------
Emp 1 V V 0 0 0 0 0
Emp 2 SL V 0 0 0 0 0
Emp 3 V V 0 0 0 0 0

FYI - V stands for Vacation and SL for Sick Leave.

Currently I am using the following 2 conditional formatting.

1> If the day = SAT or SUN then Mark a cell with one color Say RED
2> If there is a new entry of employee, Say EMP4 then do BOARDERS in that particular row from THU till WED.

On the top of the above 2 conditional formatting I want to do the following.

1> If the value in the cell = V ( Mark with one color )
2> If the value in the cell = SL ( Mark with other color )
3>
4>
....... and so on. There are total 8 possible values that can be entered.

Initially it looked very simple to me but I was not able to do it.

NOTE - The values that are populated in the cell i.e. V,SL etc changes as and when user selects a month from a given drop down. That is, for January The value in say C4 can be V where as for February C4 can be SL. The MACRO should take care of this and should change the color accordingly.

A QUICK RESPONSE TO THIS WILL BE HIGHLY APPRECIATED. Because I have created a Vacation Tracker for my project and we have not yet implemented just because of this one reason.

Thanks in advance.

Regards,
Javed.

 

[stanleydgromjr]

javedmati,

This should get you started in the right direction.

With Excel 2003 you can only have three Conditional Formats per cell. I have used the "Worksheet_Change" Event to alter the background colors of the cells for the eight values.

Excel Workbook
	E	F	G	H	I	J	K	L	M	N	O	P
5				Thu	Fri	Sat	Sun	Mon	Tue	Wed	Thu	Fri
6				1	2	3	4	5	6	7	8	9
7	Emp 1			V	V	0	0	V	V
8	Emp 2				SL	0	0	T	PH	ML	PL	CTW
9	Emp 3					0	0
10	Emp 4					0	0		W
11	Emp 5					0	0
Sheet1

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Right click the sheet tab you want the code in, and click on View Code. Paste the below code there.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("H7:AL100")) Is Nothing Then Exit Sub
    If Target.Count > 1 Then Exit Sub
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Select Case UCase(Target.Value)
        Case "CTW"
            Target.Interior.ColorIndex = 33
        Case "ML"
            Target.Interior.ColorIndex = 40
        Case "PH"
            Target.Interior.ColorIndex = 3
        Case "PL"
            Target.Interior.ColorIndex = 6
        Case "SL"
            Target.Interior.ColorIndex = 19
        Case "T"
            Target.Interior.ColorIndex = 44
        Case "V"
            Target.Interior.ColorIndex = 26
        Case "W"
            Target.Interior.ColorIndex = 35
        Case Else
            Target.Interior.ColorIndex = xlNone
    End Select
    Application.ScreenUpdating = True
    Application.EnableEvents = True
End Sub

Then add/delete the codes.


Have a great day,
Stan