conditional formatting formula to modify

[sathishsusa]

hi,

I have a leave planning sheet on that if employee plan their leaves by entering the dates it will highlight the color has green by value = 1 on leave calendar and
holiday will highlight has grey color b value = 99 ( conditional formatting)

What I need to get results If employee applying their leaves falls on holidays of their leave schedule the holidays color should to change has purple.

is it possible by adding two values can meet 100 then change the color to purple.

Please see the sample attached file and pictures.

cross posted :- https://www.excelforum.com/excel-fo...onditional-formatting-with-array-formula.html

 

[NARAYANK991]

Hi ,

See if this is OK.

I have changed the formulae only for the months of January and February.

Narayan

 

[sathishsusa]

HI Narayan sir,

first of all many Thanks for the reply and your effort
.
which u modify the formula its really awesome but if i change selection in leave type in column E. its doesn't change the color to the leave calender. can u please check why it is ?

 

[NARAYANK991]

Hi ,

Can you give some examples of what you mean by changing selection in leave type , so that I can make the same changes at my end and verify ?

Narayan

 

[sathishsusa]

Hi ,

Can you give some examples of what you mean by changing selection in leave type , so that I can make the same changes at my end and verify ?

Narayan

sir,

leave type i have three selection in column E
Annual leave = 1
in-lue leave = 4
sick leave =5

if employee they select the leave schedule as per the leave type selection either annual leave or in-lue. if annual leave falls on holidays then it will be add one more leave in annual day remaining balance and same like in -lue. for this reason i need to select the leave type. if i getting the purple color =100 (value) in holiday then i can add this value in annual day reaming balance. if i am getting the value = 99 it will add on in-lue remaining balance leave.
i hope that you understand my problems.

sorry for my English i am little weak.

 

[NARAYANK991]

Hi ,

I am sorry but I am finding it difficult to understand what your requirement is.

I have now understood the following :

You have three leave types , 1 , 4 and 5.

In your initial uploaded file , the formula in the Yearly Planner tab was putting either the value 99 or the value 1 , 4 , 5 depending on which leave type is selected.

In the Conditional Formatting rules , the colours GREEN , BLUE and BROWN were being selected based on whether the cell value was 1 , 4 or 5.

You had specified that if both 99 and 1 were possible , then they should be added to give 100 , and the colour PURPLE should be selected.

What are the additional requirements now ?

Narayan

 

[sathishsusa]

sir, what u said all are right and working perfect right now only i need if i can select the leave type green or blue (for brown color i never mind bec its sick leave only) it should indicate the color what i selecting. which you modify the holidays it should remains the same. pls see the picture to understand my requirement please..

 

[NARAYANK991]

Hi ,

See if this is OK.

Narayan

 

[sathishsusa]

sir now everything is fine if possible i select blue (4) the holiday value show 99 as grey which i mention as picture.

sorry for trouble and taking your time ..

 

[NARAYANK991]

Hi ,

Does that mean purple is to be used only if the leave type is 1 and 99 is also satisfied ?

Narayan

 

[sathishsusa]

yes correct sir what u mentioned.

 

[NARAYANK991]

Hi ,

See the attached file.

Narayan

 

[sathishsusa]

Wow, that is so amazing!! Thank you so much, it work the way I need.!! if likes can have 1000 times i can press for you sir really u made my day
I hope that you have a blessed day