Conditional Formatting - tricky one ( Atleast for me )

javedmati

Board Regular
Joined
Dec 19, 2008
Messages
213
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.
 

javedmati

Board Regular
Joined
Dec 19, 2008
Messages
213
Sure,

I will do that. Appreciate for the links which you provided :)

Regards,
Javed.
 

javedmati

Board Regular
Joined
Dec 19, 2008
Messages
213
Hi,

Looks like there is some problem. I am not able to download that software with the help of which I can add a screen shot. I can explain further if it is required....

Regards,
Javed.
 

javedmati

Board Regular
Joined
Dec 19, 2008
Messages
213
<html><head><title>Excel Jeanie HTML</title></head><body>
Excel Workbook
EFGHIJKLM
5ThuFriSatSunMonTue
6123456
7Emp 1VV00VV
8Emp 2SLSL00SLSL
9Emp 3000000
10Emp 4000000
11Emp 5000000
Monthly View


</body></html>
 

javedmati

Board Regular
Joined
Dec 19, 2008
Messages
213
<html><head><title>Excel Jeanie HTML</title></head><body>
Excel Workbook
EFGHIJKLM
5***ThuFriSatSunMonTue
6***123456
7Emp 1VV00VV
8Emp 2SLSL00SLSL
9Emp 3000000
10Emp 4000000
11Emp 5000000
Monthly View


</body></html>

Sorry Folks. I was not sure as to how I should give examples. Just came to know. Let me explain the question once again.

in H7, currently it is showing White background. I want that to be filled with some other colour. So from H7 to M11 wherever the value of the cell is V then it should change the colour.

Note - Current formatting which I already have in place is that, if H5=Sat or if H5=SUN and E7>0 then fill with coffee colour. The same conditional formatting is applied from H7 to M11. Hence J7 to k11 are marked with coffee colour.

But on the top of this, I also want the background colour to change when the value of the cell is V or SL ? ?

Hope it is clear now...
 
Joined
Jul 30, 2006
Messages
3,656
javedmati,

There are total 8 possible values that can be entered.
What are the 8 possible values? And, what color is assigned to each?
Values: Color:
1. V ?
2. SL ?
3. ? ?
4. ? ?
5. ? ?
6. ? ?
7. ? ?
8. ? ?


What version of Excel are you using?

Are you using a new worksheet for each month?


Have a great day,
Stan
 

javedmati

Board Regular
Joined
Dec 19, 2008
Messages
213
<html><head><title>Excel Jeanie HTML</title></head><body>
Excel Workbook
EFGHIJKLM
5ThuFriSatSunMonTue
6123456
7Emp 1VV00VV
8Emp 2000000
9Emp 3000000
10Emp 4000000
11Emp 5000000
Monthly View
#VALUE!


</body></html>


I have pasted the example once again along with Conditional formatting details which I have currently used. If you can just show me the way for any given 2 to 3 values and any color then I can do it for the rest. That should not be a problem.

If you can see in conditional formatting. I tried to put Green colour in LT for V but it is not getting populated. I understand that there is a overlap with other conditions but not sure as to how can I get rid of this ?

Regards,
Javed.
 
Joined
Jul 30, 2006
Messages
3,656
javedmati,

Please answer the following questions?

[/b]There are total 8 possible values that can be entered.[/b]
What are the 8 possible values? And, what color is assigned to each?
Values and Colors:
V, what color
SL, what color
3. value, what color
4. value, what color
5. value, what color
6. value, what color
7. value, what color
8. value, what color



What version of Excel are you using?

Are you using a new worksheet for each month?


Have a great day,
Stan
 

javedmati

Board Regular
Joined
Dec 19, 2008
Messages
213
Hi,

Please find below the answer to your questions.

8 possible values along with color are.

1> V - Purple
2> SL - Pink
3> T - WHITE
4> PH - Red
5> ML - Orange
6> PL - Yellow
7> CTW - Blue
8> W - Green

Note - The intension is to have unique colors for all 8 values its fine if you use Pink for V and Blue for SL and so on...

I am using Excel 2003 V 11.5612.5606

I am using the same worksheet ( same table ) to display for all the months. But at a time only 1 month will be selected.

Please let me know if you need any more information.

Regards,
Javed.
 

Forum statistics

Threads
1,085,337
Messages
5,383,041
Members
401,813
Latest member
Lucy_Wood

Some videos you may like

This Week's Hot Topics

Top